write a stored procedure to pass one value in one parameter and two values in another parameter in sql server

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Dear all,

write a stored procedure to pass one value in one parameter and two values in another parameter in sql server.

I was tried with the below code.
I got result when i pass only param1 (multiple values)
BUt when i add another param (@name)., not getting result.
Please correct me

SQL

ALTER PROCEDURE [dbo].[sp_Sample] @param1 VARCHAR(100),@name varchar(30)
AS
Begin
    DECLARE @Sql NVARCHAR(MAX)

    SET @param1 = Replace(@param1, ',', ''',''')
    SET @Sql = '(select * from tblsample where name=@name and [Escalate to] IN ('''
               + @param1 + '''))'
    exec sp_executesql @Sql
End

SQL

EXEC [sp_Sample]
@param1 = 'SIM2,SIM3'

<pre>

Thanks..............

Posted 8-Dec-14 16:47pm

Member 9409254

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

mudgilsks · Answer 1 · 2014-12-08T17:03:00

Hi,
to achive the task you need to split the variable using split function.

like this
select value from fn_Split ('par1,par2',',')

use the above query like this

IN(select value from fn_Split (@param1,','))

first execute this

create FUNCTION [dbo].[fn_Split](@sText varchar(8000), @sDelim varchar(20) = ' ')
RETURNS @retArray TABLE (idx smallint Primary Key, value varchar(8000))
AS
BEGIN
DECLARE @idx smallint,
@value varchar(8000),
@bcontinue bit,
@iStrike smallint,
@iDelimlength tinyint

IF @sDelim = 'Space'
BEGIN
SET @sDelim = ' '
END

SET @idx = 0
SET @sText = LTrim(RTrim(@sText))
SET @iDelimlength = DATALENGTH(@sDelim)
SET @bcontinue = 1

IF NOT ((@iDelimlength = 0) or (@sDelim = 'Empty'))
BEGIN
WHILE @bcontinue = 1
BEGIN
--If you can find the delimiter in the text, retrieve the first element and
--insert it with its index into the return table.
IF CHARINDEX(@sDelim, @sText)>0
BEGIN
SET @value = SUBSTRING(@sText,1, CHARINDEX(@sDelim,@sText)-1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Trim the element and its delimiter from the front of the string.
--Increment the index and loop.
SET @iStrike = DATALENGTH(@value) + @iDelimlength
SET @idx = @idx + 1
SET @sText = LTrim(Right(@sText,DATALENGTH(@sText) - @iStrike))
END
ELSE
BEGIN
--If you can’t find the delimiter in the text, @sText is the last value in
--@retArray.
SET @value = @sText
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
--Exit the WHILE loop.
SET @bcontinue = 0
END
END
END
ELSE
BEGIN
WHILE @bcontinue=1
BEGIN
--If the delimiter is an empty string, check for remaining text
--instead of a delimiter. Insert the first character into the
--retArray table. Trim the character from the front of the string.
--Increment the index and loop.
IF DATALENGTH(@sText)>1
BEGIN
SET @value = SUBSTRING(@sText,1,1)
BEGIN
INSERT @retArray (idx, value)
VALUES (@idx, @value)
END
SET @idx = @idx+1
SET @sText = SUBSTRING(@sText,2,DATALENGTH(@sText)-1)
END
ELSE
BEGIN
--One character remains.
--Insert the character, and exit the WHILE loop.
INSERT @retArray (idx, value)
VALUES (@idx, @sText)
SET @bcontinue = 0
END
END
END
RETURN
END

Member 9409254 · Answer 2 · 2014-12-08T17:21:00

SQL

Alter procedure sp_sample3
@Escalation varchar(30),@InClause NVARCHAR(100)
as

DECLARE @SafeInClause NVARCHAR(100)
SET @SafeInClause = ',' + @InClause + ','
SELECT * FROM tblSample WHERE [Escalate To]=@Escalation and CHARINDEX(',' + [Owner Dept] + ',', @SafeInClause) > 0

exec sp_sample3
@Escalation='SIM5',
@InClause='6SG,HR,Fin'

syed shanu · Answer 3 · 2014-12-08T17:31:00

Check this hope this will work for you.

SQL

ALTER PROCEDURE [dbo].[sp_Sample] @param1 VARCHAR(100),@name varchar(30)
AS
Begin
    DECLARE @Sql NVARCHAR(MAX)
 
   ---- SET @param1 = Replace(@param1, ',', ''',''')
    SET @Sql = '(select * from tblsample where name='''+ @name +''' and [Escalate to] IN ('''+ @param1 + '''))'
    exec sp_executesql @Sql
End

yusuf_ahmed · Answer 4 · 2014-12-11T02:05:00

SQL

ALTER PROCEDURE [dbo].[sp_Sample] @param1 VARCHAR(100),@name varchar(30)
AS
Begin
    DECLARE @Sql NVARCHAR(MAX)

   ---- SET @param1 = Replace(@param1, ',', ''',''')
    SET @Sql = '(select * from tblsample where name='''+ @name +''' and [Escalate to] IN ('+ @param1 + '))'
    exec sp_executesql @Sql
End

write a stored procedure to pass one value in one parameter and two values in another parameter in sql server

4 solutions

Solution 1

Solution 2

Solution 3

Solution 4

Add your solution here

Preview 0