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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2000-11-03 : 12:18:34
|
MICHAEL writes "/* *SQL SERVER 7.0* *WIN2K SP1* I'M USING THE NORTHWIND DATABASE AS AN EXAMPLE TO BUILD A PROCEDURE THAT WILL RETURN
A RECORDSET PASSING A VALUE TO THE EMPLOYEEID FIELD USING THE IN CLAUSE AND
ALSO PASSING A DATE RANGE. I BROKE UP THE SQL STATEMENT BUILD TO OUTLINE THE PROBLEM
AREA. IF THE LAST CONCATENATION OF THE @SQL VARIABLE IS INCLUDED IT WILL GENERATE AN
ERROR WHEN VARIABLE ARE PASSED TO THE PROCEDURE CALL.
HERE IS THE ERROR MESSAGE...Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'Requir'.
I HAD THE PROCEDURE PRINT OUT THE @SQL VARIABLE AND THIS IS WHAT IT LOOKS LIKE;
SELECT * FROM ORDERS WHERE EMPLOYEEID IN (1,2,3)AND RequiredDate >= "Jul 1 1996 12:00AM" AND Requir
...SEE ITS CUT OFF ...MY QUESTION IS WHY AND WHAT AM I DOING WRONG PLEASE HELP, THANKS!*/
ALTER PROC DOE
@SEARCH_ID VARCHAR(100),
@STARTDATE VARCHAR(100),
@ENDDATE VARCHAR(100)
AS
BEGIN
PRINT @STARTDATE
PRINT @ENDDATE
PRINT @STRINGPASSED
DECLARE @SQL VARCHAR(100)
SELECT @STARTDATE = CONVERT(SMALLDATETIME, @STARTDATE)
SELECT @ENDDATE = CONVERT(SMALLDATETIME, @ENDDATE)
SET @SQL = 'SELECT * FROM ORDERS WHERE EMPLOYEEID IN ('+ @SEARCH_ID +')'
SET @SQL = @SQL + 'AND RequiredDate >= "'+@STARTDATE+'"'
/*SET @SQL = @SQL + ' AND RequiredDate <= "'+@ENDDATE+'"' IF I LEAVE THIS LINE OUT, IT WORKS FINE BUT NOT THE DESIRED RESULTS */
PRINT @SQL
EXEC(@SQL)
END
" |
|
|
|
|
|
BUILDING THE SQL STRING TO EXEC(@SQL)