Problems with Stored Procedures

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)

Problems with Stored Procedures

Author

Topic

DemonDance
Starting Member

2 Posts

Posted - 2006-02-01 : 10:52:17

Hi Everyone,

I hope someone can help me with this SP.....

I've the following procedure

CREATE PROCEDURE dbo.getEventiCalendario
(
@dtEvento smalldatetime,
@groupid varchar(500)
)
AS
SET NOCOUNT ON;
DECLARE @SQL varchar(600)
SET @SQL = 'select * from Eventi
where
(data_inizio_evento ='+@dtEvento+'or ('+@dtEvento+' between data_inizio_evento and data_fine_evento))
and id_evento in (select id_evento FROM Accesso_eventi where id_gruppo in ('+ @groupid +'))'

EXEC(@SQL)
GO

The way I need to execute the sp in this way is thata the parameter @groupid is a string of few elements........the problem is that in this way I have problem with the smalldatetime parameter @dtEvento......it seems that cannot convert a string in smalldatetime, I tryed to use the convert function too but...nothing seems to work.

someone can help me?

Thank everybody in advance

Kristen
Test

22859 Posts

Posted - 2006-02-01 : 11:04:32

Hi DemonDance, Welcome to SQL Team!

You need to quote your dates, and convert them to a text string so they can be concatenated with your other string data.

(data_inizio_evento ='''+ CONVERT(varchar(24), @dtEvento, 126) +'''or ...

I suggest you put a

PRINT @SQL

before the EXEC(@SQL) so you can see what the string looks like - and you can try executing it in Query Analyser etc. to debug it.

You should also look at using parameterized queries using sp_ExecuteSQL - which will avoid some of the conversion steps, and will be more efficient.

Kristen

DemonDance
Starting Member

2 Posts

Posted - 2006-02-02 : 03:09:19

Hi Kristen thank you very much for the warm welcome
and obviously for your help

Your solution changed entirely my situation........but make born another problem that surely was already present but I couldn't see.....

Now for the Date parameters is all ok..........but not for the other parameter......
I'll explain it better....
I've an ASP.NET application that call this procedure, I put in the parameter i pass to the SP an array of string and I've the following result
"'2','5','8','55','222','552'"

when i pass this to the procedure it should became something of this kind ''2','5','8','55','222','552''...

I can I solve it?

Can you help me again?

Thanks a lot anyway

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-02-02 : 03:38:02

declare @s varchar(100)
set @s='"''2'',''5'',''8'',''55'',''222'',''552''"'
print @s
set @s=replace(@s,'"','''')
print @s

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-02-02 : 04:28:18

"Can you help me again?"

Obviously not when Madhi's in a tearing hurry!

Either pass the parameter list without the embedded single quotes, or strip them out in your Sproc using REPLACE() - as Madhi said.

However, food for thought:

You are creating dynamic SQL in your SProc which [I am guessing] is solely to get around the need for an IN list which can be parameterised.

The use of dynamic SQL means that your query's execution plan will not be cached (or at least not as well as normal SProc queries) [poorer performance] AND your users will need permissions on the underlying tables [rather than just Execute permissions on the Sproc].

An alternative would be to "split" the @groupid list into a temporary table, and then just JOIN that temporary table to Accesso_eventi to match the records you want.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=WHERE+IN+@MyCSV - see also "Best split functions" just below

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources