problem with "IN" - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem with "IN"

Author

Topic

pacastillo24
Starting Member

3 Posts

Posted - 2008-01-30 : 12:29:03

Hi,

I am using this stored procedure in my program, but I want to know want is wrong with this;

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[ObtenerMenuByRol]
@rol varchar(50)
AS
SET NOCOUNT ON;
SELECT ID, ParentID, Texto, URL, fechaCreacion, Rol
FROM TM0125
WHERE (Rol In(@rol))

@rol get this parameter 1 or 2 or 3

but if I pass to this 1,2 from my code this has errors, anyone can give me some tips to solve this?

if I copy

USE [Saig]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[ObtenerMenuByRol]
@rol = '1','2'SELECT 'Return Value' = @return_value
GO

this says:

Msg 119, Level 15, State 1, Line 4
Must pass parameter number 2 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

I dont know about transact sql only the basic.

kind regards,

Pablo

pacastillo24
Starting Member

3 Posts

Posted - 2008-01-30 : 12:39:48

I read the FAQ

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=25830

Thanks!

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-01-30 : 13:52:00

You're getting the error because you are sending two parameters but your procedure only accepts one. I found this article on how to pass a string of IDs and split them out inside the parameter:

http://blog.krisvandermast.com/UsingACommaDelimitedStringWithIdsAsInputParameterForASQLQuery.aspx

Tom Rupsis
Granite Peak Systems
Phone: 406-672-8292
Email: trupsis@granitepeaksys.com
LinkedIn: www.linkedin.com/in/trupsis

pacastillo24
Starting Member

3 Posts

Posted - 2008-01-30 : 17:48:41

uh theses solution are a little hard for a newbie same as me.

I dont understand anything

tprupsis
Yak Posting Veteran

88 Posts

Posted - 2008-02-03 : 23:11:00

The article provides the code for a SPLIT function. Create a function in your database called SPLIT using this code. Then, change your stored procedure from

WHERE (Rol In(@rol))

to

WHERE (Rol In(SPLIT(@rol,',')))

Then, when you call the procedure, pass the parameter as @rol = '1,2'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources