How to update using a SP? - 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 2000 Forums

SQL Server Development (2000)

How to update using a SP?

Author

Topic

rajaseeth
Starting Member

3 Posts

Posted - 2008-10-24 : 14:29:43

Hi,

This is the first time i am writing a SP. My basic requirements is that i have to disable a set of users by passing their emailid. I will pass a set of emailid's from my program to the SP as a string with comma seperated. I have written the following SP for that

CREATE PROCEDURE disable_users_sp
--INPUT PARAMETERS
@EmailID AS VARCHAR(4000),
--OUTPUT PARAMETERS
@ErrorCode INT OUT,
@ErrorMessage VARCHAR(200) OUT
AS
BEGIN TRANSACTION
BEGIN

SET @SQLQry='update login set isdisable = 1 where EmailID IN '+@EmailID +')'
EXEC(@SQLQry)

END

SET @ErrorCode = 0
SET @ErrorMessage ='Users Disabled Sucessfully'
COMMIT TRANSACTION
When i enter the following values
EXEC disable_users_sp('test@test-e.com,test1@test.com')

I am getting an error that 'e is not a valid index or identifier' or somethig like that.. Even though i have a value ''test@test-e.com' under the column EmailID..

Can someone tell me what could be the problem?. Please it's urgent..

Thanks,
Rajagopalan.S

hanbingl
Aged Yak Warrior

652 Posts

Posted - 2008-10-24 : 16:22:05

TRY:


EXEC disable_users_sp('''test@test-e.com'',''test1@test.com''')

Your SP is fine.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-10-29 : 08:25:22

Dont use braces

EXEC disable_users_sp '''test@test-e.com'',''test1@test.com'''

Also see how to handle single quotes
http://sqlblogcasts.com/blogs/madhivanan/archive/2008/02/19/understanding-single-quotes.aspx

Madhivanan

Failing to plan is Planning to fail

ursangel
Starting Member

17 Posts

Posted - 2008-10-29 : 08:37:23

no need of open and closing brackets when executing the procedure.
Remove it and try
EXEC disable_users_sp 'test@test-e.com,test1@test.com'.
But i doubt whether the where condition will work properly for more than one email address given as comma seperated.

try inserting the email address into a table variable and join it with your where condition

Regards
Angel

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 09:02:40

Also be careful. Make sure you validate any string that gets passed to your stored proc.

Consider what your stored proc will do if the following string is passed to it

DON'T TRY THIS ON PRODUCTION -- It is designed to hurt you
EXEC diable_users_sp '''test1@test-2.com'',''@test2@test3.com''); DROP TABLE login; --'

It would be better in this case to use some sort of string splitting function and generate a table or return values. Then use IN (or JOIN) on that.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 09:09:31

For a safe way of doing this. Look for string splitting on this forum. there are many examples of quick string splitting functions that will return a table of values based on a string you send it.

-------------
Charlie

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-10-29 : 09:13:27

http://xkcd.com/327/

-------------
Charlie

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources