On 11 Apr 2005 08:44:24 -0700, Caro wrote:
[color=blue]
>I have a stored procedure spGetAccessLogDynamic and when I try to call
>it I get the following error:
>
>Server: Msg 2812, Level 16, State 62, Line 1
>Could not find stored procedure 'S'.
>
>I dont know why because I dont have anything refering to stored
>procedure 'S'
>
>I have ran my SQL String with sample values and it works fine. So I
>am presuming that it is some kind of syntax error in my stored
>procedure but have tried everything and cant find it!
>
>Anyway here is the sample data I am using to call it and my sp
>
>Exec spGetAccessLogDynamic '24', '2005/07/04 00:00:00 AM', '2005/11/04
>00:00:00 AM', 'TimeAccessed DESC'
>
>
>CREATE PROCEDURE spGetAccessLogDynamic
>(
> @PinServiceID varchar (4),
> @StartDate varchar(40),
> @EndDate varchar(40),
> @SortExp varchar (100)
>)
>
>AS
>
>-- Create a variable @SQL Statement
>DECLARE @SQLStatement varchar
>
>-- Enter the Dynamic SQL statement into the variable @SQLStatement
>SELECT @SQLStatement = ( 'SELECT A.PinValue,A.TimeAccessed,
>C.Forename, C.Surname
> FROM AccessLog A, Members C, Pins P
> WHERE P.PinValue = A.PinValue AND
>P.MemberID = C.MemberID AND A.PinServiceID= ''' + @PinServiceID + '''
>AND A.TimeAccessed BETWEEN dbo.func_DateMidnightPrevious( ''' +
>@StartDate + ''' ) AND dbo.func_DateMidnightNext( ''' + @EndDate
>+''')
> GROUP BY A.PinValue,
>A.TimeAccessed, C.Forename, C.Surname
> ORDER BY ' + @SortExp)
>
>-- Execute the SQL statement
>EXEC ( @SQLStatement)
>GO
>
>Any help would be very very much appreciated!!!!!!
>
>Thanks
>Caro[/color]
Hi Caro,
1. Default length for varchar is 1. Since you didn't specify length for
@SQLStatement, it has length 1. The value of the variable is 'S' when
the EXEC statement is executed.
2. When debugging dynamic SQL, a simple method is to temporarily replace
the EXEC (@SQLStatement) statement with PRINT @SQLStatement. That allows
you to visually review the code that actually will be executed.
3. The date format you're using is ambiguous. Is 2005/07/04 april 7th or
july 4th? These are the only formats that are guaranteed to be always
interpreted correctly by SQL Server:
* yyyymmdd (for date only - time defaults to midnight. No dashes,
slashes, dots or other interpunction!!))
* yyyy-mm-ddThh:mm:ss.ttt (for date and time - the milliseconds part
is optional; the dashes, colons and capital T are required).
4. Your code might be vulnerable to SQL Injection attacks. What is the
source of the @SortExp parameter? If it's from an input box in your
application, then what would prevent a user from calling the proc with
these arguments:
EXEC spGetAccessLogDynamic '24', '20050704', '20051104',
'TimeAccessed DESC; DROP TABLE Members --'
Check out this site:
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
