Dynamic stored procedure

Question

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.

Dharmraj Thakur · 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 Thakur · Answer

--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 Vahid · Answer

Dharmraj sir, i attached table structures, based on Batch IDs i nhave to get all user data.

Dharmraj Thakur · Answer

First i should have table and data in that ... Then i can do that

Shaik Vahid · Answer

sir, could you please send me query.

Dynamic stored procedure

Answers (5)