July 21, 2008 at 5:47 pm
I am new to this.
This is my stored procedure in SQL server 2005:
ALTER PROCEDURE [dbo].[strprocA]
@STARTDATE datetime = NULL
AS
DECLARE
@WHERE VARCHAR(2000),
@SELECT VARCHAR(1000),
@SQL VARCHAR(8000)
SET @SELECT = "SELECT ADATETIME FROM viewA"
SET @WHERE = ""
IF @STARTDATE is not null
BEGIN
IF @WHERE <> ""
BEGIN
SET @WHERE = @WHERE + " AND "
END
SET @WHERE = @WHERE + " ADATETIME >= @STARTDATE "
END
IF @WHERE <> ""
BEGIN
SET @WHERE = " WHERE " + @WHERE
END
SET @SQL = @SELECT + @WHERE
EXEC(@sql)
ADATETIME is datetime. Why do I get the error "Must declare the scalar variable "@STARTDATE"?
July 21, 2008 at 10:36 pm
SET @WHERE = @WHERE + ' ADATETIME >= '''+ cast(@STARTDATE as varchar(50)) + ''''
use the above statement instead.
July 21, 2008 at 10:55 pm
I have to ask - why are you using dynamic SQL? There is no need in this case - just write the query and use the variable as you wanted.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
July 22, 2008 at 5:47 am
Since you are new to this, please take a piece of advice.
Don't use dynamic SQL.
Especially since you're just getting started. Do the work to figure out how to write good TSQL statements. It will serve you better in the long term.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2008 at 7:59 am
If you haven't read Sommarskog's piece on dynamic SQL and how to avoid it when you want to create a dynamic WHERE clause, then you really should do so now!
http://www.sommarskog.se/dynamic_sql.html
It's not too hard to make a dynamic WHERE clauses without having to use dynamic SQL, plus doing it without dynamic SQL means you will have a lower chance of SQL Injection (Where does the WHERE clause in the SP come from? User input? Could someone type in DROP DATABASE MASTER;--'?) plus the other problems outlined far better by Sommarskog than I can do here!
Let me know if you get stumped & I'll try and help some more.
July 22, 2008 at 8:48 am
SSC Veteran nailed it. Appreciated thanks to SSC Veteran, and thanks for the advice from other respondents, which will not go unheeded. For the moment, I am following the beaten track. The SP is called from VB which passes parameters from an input screen, which in turn are processed by the SP and the results of the query and the original parameters are then passed on to Crystal Reports, if that helps.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply