468,242 Members | 1,705 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,242 developers. It's quick & easy.

Help with dynamic SQL Stored Procedure

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
Jul 23 '05 #1
2 5431
On 11 Apr 2005 08:44:24 -0700, Caro wrote:
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


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)
Jul 23 '05 #2
Thanks so much Hugo.

I now have it all up and running!!!

My SortExp is from an asp datagrid's bound columns but thanks for the
warning about injection attacks!

Thanks again

Caro
Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Guinness Mann | last post: by
7 posts views Thread by Michael C# | last post: by
18 posts views Thread by Bill Smith | last post: by
1 post views Thread by peaceburn | last post: by
7 posts views Thread by Ronald S. Cook | last post: by
reply views Thread by SOI_0152 | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.