abt Stored Procedure ...

[rameshcse2005]

hello guys ..

today i came with a Common question...

i am developing a website in that, I am using Stored procedure for all queries ( select, insert, update, delete )

is it good to run all queries in stored procedure ... or we should run only the SELECT queries by SP .... i need ur valuable suggestion ...


cheers ...

[rameshcse2005]

i am waiting for some experienced person's answer ... is anybody here ??????

[deepuv04]

i am waiting for some experienced person's answer ... is anybody here ??????

hi,
You can use any sql statement inside your stored procedure....

According to me if you use stored procedures in your applications it will be good, if you need any database change just manipulate your sp, no need of rebuild or recompile your application.. all you need is organizing your application and stored procedures. better the design you have better the result you get

[debasisdas]

You can use all the DML statments inside a procedure.

[rameshcse2005]

Thank you very much for ur valuable suggestion .... :-)


and i have another doubt ?

how to pass multiple value in same parameter in SQL stored procedure ??

for example i have to write stored procedure for this query

DELETE FROM tableName where ID in (id1,id2,id3) ?

[debasisdas]

You need to pass a refcursor to the procedure to dynamically pass any number of parameters for the IN operator..

[rameshcse2005]

Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)

[deepuv04]

Thanks for ur suggestion ... i w'l try & let u know if any doubts ... :-)

hi
you can do this as follows

send the id as a string seperated by ,(comma)

in your stored procedure use the following query

DELETE FROM tableName where ID in
( select id from tableName
where (','+isnull(@parameter,convert(varchar(10),Id) )+',') like
('%,'+convert(varchar(20),Id)+ ',%')
)

In the above query if you pass null value to the @Parameter then all the records will be deleted, if you send the id values seperated by coma then specific records only will be deleted