Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Unanswered: Build Dynamic Query Using sp_executesql

    Hi there,

    I am trying to build a proc that uses a loop to import data into several tables. The data is copied into the appropriate table according to the contents of the variable @PracticeCode. I am also trying to add a date value to each record as it is added to the table. I thought that the best way to do this would be t use the sp_executesql stored proc. but I am having difficulty getting it to work. Here's what I have done so far:

    -- insert data into proper tables with extract date added
    SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
    SurgeryKey,'+
    @extractDate+',
    ClinicianCode,
    StartTime,
    SessionGroup,
    [Description],
    SurgeryName,
    Deleted,
    PremisesKey
    FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

    EXEC master..sp_executesql @SQLString

    And here's the error message that I get:

    Server: Msg 241, Level 16, State 1, Line 90
    Syntax error converting datetime from character string.

    I understand why I am getting this error I just can't seem to fix it. I've consulted BOl and have tried various Parameter combinations but to no avail.

    Can anyone help?

    Thanks

  2. #2
    Join Date
    Mar 2004
    Location
    Glasgow, Scotland
    Posts
    107

    Figured it out!

    Managed to figure it out all by myself. Think I was a bit premature in posting this one...sorry :-)

    Here's my solution in case anyone is intertested:

    DECLARE @SQLString nvarchar(500)
    DECLARE @Parameters nvarchar(500)


    SET @Parameters = '@Date DateTime'
    -- insert data into proper tables with extract date added
    SET @SQLString ='INSERT INTO GMS_48hrAccess.dbo.tbl_Surgery'+@PracticeCode+' SELECT
    SurgeryKey,
    @Date,
    ClinicianCode,
    StartTime,
    SessionGroup,
    [Description],
    SurgeryName,
    Deleted,
    PremisesKey
    FROM GMS_48hrAccess.dbo.tbl_SurgeryIn'

    EXEC master..sp_executesql@SQLString,@Parameters,@Date= @ExtractDate

Posting Permissions

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