Post reply

Need help with dynamic sql syntax and convert

  • July 12, 2005 at 7:52 am

    #102636

    I am trying to write a dynamic sql that uses variable dblinks to servers and db's.   In the process I want to retrieve data from the selected table.   I am having trouble formatting the correct syntax to make my cast or converts work along with the use of substrings.   Can anyone make suggestions as to what is needed to get this to work properly.   I don't show an error when it runs,  it just doesn't return anything.

    I have attached some code.

    Thanks, Pat

     

    DECLARE @SQLString  NVARCHAR(500)

    DECLARE @ParmDefinition NVARCHAR(500)

    DECLARE @uidEventID varchar (50)

    DECLARE @Rowcount int

    DECLARE @RecordID varchar(3)

    DECLARE @vch_RecordStr1 varchar(250)

    DECLARE @vch_RecordStr2 varchar(250)

    DECLARE @vch_RecordStr3 varchar(250)

    DECLARE @vch_RecordStr4 varchar(250)

    DECLARE @server  VARCHAR (30)

    DECLARE @DBNAME  VARCHAR (30)

    DECLARE @in_vchServer  varchar (30)

    DECLARE @in_vchDBName  varchar (30)

    DECLARE @in_vchRecordID  varchar (3)

    set @in_vchServer = 'FSIDB'

    SET @in_vchDBName = 'FSIDB'

    SET @SQLString = 'SELECT top 1 ' + @uidEventID + ' = CONVERT(VARCHAR(50),SLOTID)' +

         ' FROM ' + @in_vchServer + '.' + @in_vchDBName + '.dbo.FMSSTYLE'

    EXEC (@SQLString)

    select @uidEventID AS ID

  • July 12, 2005 at 8:14 am

    #573384

    Have you tried printing the dyn. query and executing it in QA instead? Maybe that'll pop up something.

  • July 12, 2005 at 9:19 am

    #573435

    as suggested put a Print @SqlString in there before the execute,

    Also in String concatenation a null + String = NULL

    I don't see a value being set for @uidEventID which would render a null.

     

  • July 12, 2005 at 9:26 am

    #573446

    I've never been able to set a value in a paramater in a dynamic string (I don't no if you can or not) but you can insert it into a temp table like this:

    DECLARE @SQLString  NVARCHAR(500)

    DECLARE @ParmDefinition NVARCHAR(500)

    DECLARE @uidEventID varchar (50)

    DECLARE @Rowcount int

    DECLARE @RecordID varchar(3)

    DECLARE @vch_RecordStr1 varchar(250)

    DECLARE @vch_RecordStr2 varchar(250)

    DECLARE @vch_RecordStr3 varchar(250)

    DECLARE @vch_RecordStr4 varchar(250)

    DECLARE @server  VARCHAR (30)

    DECLARE @DBNAME  VARCHAR (30)

    DECLARE @in_vchServer  varchar (30)

    DECLARE @in_vchDBName  varchar (30)

    DECLARE @in_vchRecordID  varchar (3)

    set @in_vchServer = 'FSIDB'

    SET @in_vchDBName = 'FSIDB'

    create table #uid (uidEventID varchar(50))

    set @SQLString = 'SELECT top 1 CONVERT(VARCHAR(50),SLOTID)' +

         ' FROM ' + @in_vchServer + '.' + @in_vchDBName + '.dbo.FMSSTYLE'

    insert into #uid (uidEventID)

    EXEC (@SQLString)

    select @uidEventID = uidEventID from #uid

    print @uidEventID

    drop table #uid

     

  • July 12, 2005 at 9:26 am

    #573445

    Forgot to check that one. Probabely because when I see null in the print I know I forgot to initiate a value .

  • July 12, 2005 at 9:33 am

    #573458

    AND

    The use of TOP implies the use of ORDER BY or you may get the record that you are not expecting

    set @SQLString = 'SELECT top 1 CONVERT(VARCHAR(50),SLOTID)' +

         ' FROM ' + @in_vchServer + '.' + @in_vchDBName + '.dbo.FMSSTYLE ORDER BY SOMETHING'


    * Noel

  • July 12, 2005 at 9:56 am

    #573484

    Maybe that was just for testing... but you're right of course .

  • July 12, 2005 at 10:06 am

    #573493

    If you want to assign a value to a parameter use sp_executeSQL

    set @SQLString = 'SELECT top 1 @uidEventID = MCONVERT(VARCHAR(50),SLOTID)' +

         ' FROM ' + @in_vchServer + '.' + @in_vchDBName + '.dbo.FMSSTYLE Order By Something'

    exec sp_executeSql @SqlString, N'@uidEventID varchar(50) OUTPUT', @uidEventID = @uidEventID OUTPUT

    -- Tested

    USe Pubs

    Declare @Myvalue varchar(10)

    declare @sqlstring nvarchar(1000)

    Set @SqlString = 'Select top 1  @Myvalue = customerId from orders'

    exec sp_executeSql @sqlstring, N'@Myvalue varchar(10)OUTPUT', @Myvalue = @Myvalue OUTPUT

    select @MyValue

  • July 12, 2005 at 10:22 am

    #573520

    Thank all of you for your replies,  it is helping.

    Here is a better look at the entire string.   I am not sure at this time if the substrings are going to work.  Does anyone see a problem with these?

    Is there a good source anywhere that provides good info on the specific syntax for dynamic sql?

    Thanks 

     

    SET @SQLString = 'SELECT top 1 ' + @uidEventID + ' = CONVERT(VARCHAR(50),SLOTID)'

         + @vch_RecordStr1 + '= SUBSTRING(FMS_DATA,1,250), '

         + @vch_RecordStr2 + '= SUBSTRING(FMS_DATA,251,250), '

         + @vch_RecordStr3 + '= SUBSTRING(FMS_DATA,501,250), '

         + @vch_RecordStr4 + '= SUBSTRING(FMS_DATA,751,250) '

         + ' FROM ' + @in_vchServer + '.' + @in_vchDBName + '.dbo.FMSSTYLE

         WHERE RECTYPE = ' + @in_vchRecordID

             + ' AND PROCESS_SW = ''N''

                      ORDER BY SLOTID'

  • July 12, 2005 at 10:28 am

    #573527

    The Curse and Blessings of Dynamic SQL

    Dynamic Search Conditions in T-SQL

    Arrays and Lists in SQL Server

    Also this is not gonna work because @uidEventID will not be a variable in the exec statement. Check Ray M's answer for a solution.

  • July 12, 2005 at 11:10 am

    #573569

  • July 12, 2005 at 11:29 am

    #573585

    I missed it too at first . But you have the best solution yet... you just have to make a small correction and repost .

  • July 12, 2005 at 12:03 pm

    #573602

    Thanks for your help and advice.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply