August 11, 2011 at 1:19 pm
I have created a stored procedure like below
Create procedure sp_logins
As
Select name from sys.syslogins where name not IN ('XYZ', 'SQL')
End
Now I want to change it to
Alter procedure sp_logins
(
@loginnames varchar(8000)
)
As
Select name from sys.syslogins where name not IN (@loginnames)
END
So when executed like
EXEC sp_logins @loginnames= 'XYZ', 'SQL'
I'm getting error too many arguments passed to the stored procedure...
I remember the workaround to this by creating a function..
Any help would be helpful... Thanks in advance
August 11, 2011 at 1:52 pm
@loginnames= 'XYZ', 'SQL'
That is trying to assign two string values to a single variable.
You might need something like
@loginnames= '''XYZ'', ''SQL'''
Of course you need to have a plan of actually splitting that inside your proc.
If you pass the above, the following will NOT work as you expect.
Select name from sys.syslogins where name not IN (@loginnames)
It will look for records where name = 'xyz, sql'. I suspect that is not what you want. You can pass multiple parameters like that in a delimited string but you will need something to split them. for this you should take a look as Jeff Moden's article about splitting delimited strings[/url].
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2011 at 2:02 pm
Create a function that takes a comma delimited sting and returns a two column table. (ID, LookupValue)
Select yada
FROM MyTable
Left Join fnc_tableFromString(@argMyString) theName ON myTable.name = theName.name
where myTable.id is null
August 11, 2011 at 2:11 pm
Daryl AZ (8/11/2011)
Create a function that takes a comma delimited sting and returns a two column table. (ID, LookupValue)Select yada
FROM MyTable
Left Join fnc_tableFromString(@argMyString) theName ON myTable.name = theName.name
where myTable.id is null
Yes look at Jeff's article. His function is insanely fast. It has been tweaked and tweaked by some of the smartest sql folks in the business to make it fast!!!!!! There is no way in the world i would try to roll my own function for this. Take a look as it contains a lot more information than just a table splitting function.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 11, 2011 at 2:21 pm
Sean Lange (8/11/2011)
Daryl AZ (8/11/2011)
Create a function that takes a comma delimited sting and returns a two column table. (ID, LookupValue)Select yada
FROM MyTable
Left Join fnc_tableFromString(@argMyString) theName ON myTable.name = theName.name
where myTable.id is null
Yes look at Jeff's article. His function is insanely fast. It has been tweaked and tweaked by some of the smartest sql folks in the business to make it fast!!!!!! There is no way in the world i would try to roll my own function for this. Take a look as it contains a lot more information than just a table splitting function.
Thanks Guys...Looking at it..
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply