Dynamic stored procedure

Shaik Vahid

8y777SQL

I want a stored procedure, which can take muliple values as a single parameter in mysql.

Suppose, i selected N no.of cities in list box based on that i should get a user details.

Thanks in advance.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Dharmraj Thakur8y ago
Accepted answer
You should get all that cityid and make one string by comma saperated and pass that string in sql... in sql split the string and you will get your result
Dharmraj Thakur8y ago
--first create a function
CREATE FUNCTION [dbo].[Split]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)

--and call that function to get splitted data
DECLARE @data VARCHAR(MAX)
DECLARE @Delimeter VARCHAR(MAX)

SET @data = '1,2';
SET @delimeter = ',';

select * from User_Roles where UserID in (SELECT * FROM dbo.Split(@data, @delimeter))
Shaik Vahid8y ago
Dharmraj sir, i attached table structures, based on Batch IDs i nhave to get all user data.
Dharmraj Thakur8y ago
First i should have table and data in that ... Then i can do that
Shaik Vahid8y ago
sir, could you please send me query.