sqlserver stroed proc

kumar yoga

10y953

how to create stored porcedure using in clause sqlserver

mycode

create proc getemp(@ename varchar(25))

begin

select * from emp where ename in(@ename)

end

exec getemp 'aa','bb'

sqlserver showing error two many parametr passed

5 Replies

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

Prasham Sabadra10y ago
Accepted 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
0
kumar yoga10y ago
thanks
0
Raja T10y ago
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 'AAA,BBB'
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
0
Avikshith Aradhya10y ago
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.
0
Akshay Phadke10y ago
Create proc test
@Employeeid varchar(50)
AS
BEGIN
EXEC ('select * from Employee where Employee_id in ('+Employeeid+')')
END
0