sqlserver stroed proc

Question

how to create stored porcedure using in clause sqlserver mycode create proc getemp(@ename varchar(25)) as begin select * from emp where ename in(@ename) end go exec getemp 'aa''bb' sqlserver showing error two many parametr passed

Prasham Sabadra · Answer

Hi Please have a look once following links. http://stackoverflow.com/questions/1525126/sql-in-clause-in-stored-procedurehow-to-pass-values http://stackoverflow.com/questions/11863848/dynamically-creating-the-in-clause-in-a-stored-procedure http://mitchelsellers.com/blogs/2008/08/08/using-the-in-clause-with-stored-procedures.aspx

kumar yoga · Answer

thanks

Raja T · Answer

Hi  i have create new table and insert data into table and create one store procedure. Please check below code..I hope helpful for you.. Create Table: CREATE TABLE GETEMP (ENAME VARCHAR(50)) Insert Data INSERT INTO GETEMP VALUES('AAA')('BBB')('CCC')('DDD') Select Query SELECT * FROM GETEMP Store Procedure CREATE PROC SP_GETEMP(@ENAME VARCHAR(25)) AS BEGIN PRINT @ENAME SELECT * FROM GETEMP WHERE ENAME IN(SELECT DATA FROM DBO.SPLIT(@ENAME'')) END GO Execution : EXEC SP_GETEMP 'AAABBB'​ Split Function : CREATE FUNCTION [dbo].[Split](@data NVARCHAR(MAX) @delimiter NVARCHAR(5)) RETURNS @t TABLE (data NVARCHAR(max)) AS BEGIN DECLARE @textXML XML; SELECT @textXML = CAST(' ' + REPLACE(@data @delimiter ' ') + ' ' AS XML); INSERT INTO @t(data) SELECT T.split.value('.' 'nvarchar(max)') AS data FROM @textXML.nodes('/d') T(split) RETURN END

Avikshith Aradhya · Answer

if you pass multiple values through one parameter in a stored procedure the parameter which you have sent will be in one string and that one big string should be split you can use Userdefined function to do this task.

Akshay Phadke · Answer

Create proc test @Employeeid varchar(50) AS BEGIN EXEC ('select * from Employee where Employee_id in ('+Employeeid+')') END