need help with procedure parameter syntax

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

Transact-SQL (2000)

need help with procedure parameter syntax

Author

Topic

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-26 : 09:21:10

I have a varchar that I'm passing in as an argument in a stored procedure called @FullDate_input. I have another parameter called @num_input of type int, as well. This is the complete execution:

execute sp_CalcProc 5, 'FullDate between ''12/12/1980'' and ''12/12/1982'' '

The problem is I'm getting a syntax error near @FullDate_input in the procedure itself, where I call a select statement including said parameter:

Select * from Table_tree
where num = @num_input and @FullDate_input

Can somebody please inform me what is incorrect with my select statement? Thank you in advance.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-26 : 09:43:49

You shouldnt send where condition as parameter which can easily lead to sql injection

Use dynamic SQL

EXEC('
Select * from Table_tree
where num = '+cast(@num_input as varchar(10))+' and '+@FullDate_input
)

But I would say, use three parameters @num_input, @start_date and @end_date and modify your code as

Select * from Table_tree
where num = @num_input and FullDate between @start_date and @end_date

Madhivanan

Failing to plan is Planning to fail

SQLIsTheDevil
Posting Yak Master

177 Posts

Posted - 2007-10-26 : 10:58:11

Yea, I think there is a problem with including variables of varchar type in a select statement, as I attempted to pass in a variable from within the procedure itself inside the select statement and I get a syntax error. Thanks for the heads up about the injection.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-29 : 02:22:58

quote:
Originally posted by SQLIsTheDevil

Yea, I think there is a problem with including variables of varchar type in a select statement, as I attempted to pass in a variable from within the procedure itself inside the select statement and I get a syntax error. Thanks for the heads up about the injection.

I hope you would follow my second suggestion

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources