Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: Using Dynamic SQL with sp_sqlexec

    I've been working on a stored procedure trying to use sp_sqlexec to user a parameter for which table to store to. I used the following code, and I'm not getting any errors, but nothing is being entered into the database. Does anyone know what the problem is?

    use TeeTime
    if exists (select routine_name from information_schema.routines where routine_type = 'procedure' and routine_name = 'udpAddCourse')
    drop procedure udpAddCourse
    go

    create proc udpAddCourse
    @numDays int = 5,
    @numMinutes int =7 ,
    @mintime datetime = '6:30:00 AM',
    @maxtime datetime = '8:45:00 PM',

    as

    declare @newCourseID int
    declare @mindate smalldatetime
    declare @maxdate smalldatetime
    declare @date smalldatetime
    declare @time smalldatetime
    declare @InsertStatement varchar (255)
    declare @userID int

    set @newCourseID = (select max(course_id) from course_info)+1
    set @insertStatement ='insert into course90000003 '
    set @insertStatement = @insertStatement + cast(@newCourseID as varchar)
    set @insertStatement = @insertStatement + '(date_id,selectdate,times,is_available) '
    set @insertStatement = @insertStatement + 'Values('
    set @insertStatement = @insertStatement + cast(@userID as varchar) +',' + cast(left(@date,11) as varchar)+ ',' + cast(right(@time,7)as varchar)+',0)'

    set @mindate = getdate()
    set @maxdate = dateadd(day,@numDays,@mindate)
    set @date = @mindate
    set @time = @mintime

    while @date <= @maxdate
    Begin
    while @time <= @maxtime
    Begin
    set @userID = ((cast(datepart(hh,@time) as varchar))+(cast(datepart(mi,@time)as varchar))+ (cast(datepart(day,@date) as varchar)))
    exec sp_sqlexec @insertStatement
    set @time = DATEADD(mi, @numMinutes, @time)
    end
    set @time = @mintime
    set @date = DATEADD(d,1,@date)
    end

    I call the proc using this code

    exec udpAddCourse
    5,
    5,
    '6:30AM',
    '8:45PM'

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    sp_sqlexec?
    Which version are you using.

    Do you mean sp_executesql or just exec?
    If so the variable should be a nvarchar and is 255 chars big enough?

    Also check the insert statement - it looks like it is invalid. Always display dynamic sql before trying to execute it.

    Also dates and char fields should have quotes round them.

  3. #3
    Join Date
    Aug 2003
    Posts
    7
    I just upgraded to SQL Server 2000 a couple months ago. Has the syntax changed for this command? Could you please give me an example of what is should look like? Thanks!

  4. #4
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    sp_sqlexec is not supported.

    To run a dynamic sql statement use exec () or sp_executesql. Both will execute a string.

    Your sattement looks like it will generate something like

    insert course90000003 25 (date_id,selectdate,times,is_available) values (...

    Which would be invalid.

    exec would just be

    exec (@insertStatement)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •