Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41

    Unanswered: Concatination problem

    Hi all,

    I need to get in my stored procedure name of 'result' field as parameter.
    I'm trying to implement that using sp_sqlexec but everytime I get error...
    Any ideas?
    -----------------------------------------------------------------------------------------
    Create procedure MyProc
    @CurClientID varchar(10)
    @CurCounterName varchar(20)

    AS

    declare @ExecString varchar(300)

    set @ExecString = 'Select ' + @CurCounterName +
    ' FROM Client WHERE ClientID=' + @CurClientID

    exec sp_sqlexec @ExecString

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Re: Concatination problem

    Originally posted by yurich
    Hi all,

    I need to get in my stored procedure name of 'result' field as parameter.
    I'm trying to implement that using sp_sqlexec but everytime I get error...
    Any ideas?
    -----------------------------------------------------------------------------------------

    this should work :

    Create procedure MyProc
    @CurClientID varchar(10)
    @CurCounterName varchar(20)

    AS

    declare @ExecString varchar(300)

    set @ExecString = 'Select ' + @CurCounterName +
    ' FROM Client WHERE ClientID=' + @CurClientID

    exec sp_sqlexec @ExecString
    Create procedure MyProc
    @CurClientID varchar(10)
    @CurCounterName varchar(20)

    AS

    declare @ExecString varchar(300)

    set @ExecString = 'Select ' + @CurCounterName +
    ' FROM Client WHERE ClientID=' + @CurClientID

    exec (@ExecString)
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    The holy book[SQL Server Books online] says so :
    sp_sqlexec provided a convenient way for SQL Server database clients and servers to send a language statement of any format to an Open Data Services server application. Removed; no longer available. Remove all references to sp_sqlexec.
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    It does not help...

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    What is the error you are getting ???
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    Syntax error converting the varchar value ....

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: Concatination problem

    Originally posted by yurich
    Hi all,

    I need to get in my stored procedure name of 'result' field as parameter.
    I'm trying to implement that using sp_sqlexec but everytime I get error...
    Any ideas?
    -----------------------------------------------------------------------------------------
    Create procedure MyProc
    @CurClientID varchar(10),-- just add comma !!!!
    @CurCounterName varchar(20)

    AS

    declare @ExecString varchar(300)

    set @ExecString = 'Select ' + @CurCounterName +
    ' FROM Client WHERE ClientID=' + @CurClientID

    --exec sp_sqlexec @ExecString
    exec(@ExecString)

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Snail you beat me to it ,

    Yurich ...thats the only error i could find in the code .. rest the code seems fine
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I imagine your missing the quotes...
    Code:
    DECLARE @SQL varvhar(8000)
    SELECT @SQL =     'Select ' + @CurCounterName 
    		+ ' FROM Client WHERE ClientID=' + '''' + @CurClientID + ''''
    SELECT @SQL
    
    exec (@ExecString)
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Nov 2003
    Location
    Israel
    Posts
    41
    Originally posted by Brett Kaiser
    I imagine your missing the quotes...
    Code:
    DECLARE @SQL varvhar(8000)
    SELECT @SQL =     'Select ' + @CurCounterName 
    		+ ' FROM Client WHERE ClientID=' + '''' + @CurClientID + ''''
    SELECT @SQL
    
    exec (@ExecString)
    It works! Thanks a lot. Now I need to get out result of this query to local variable:

    DECLARE @SQL varvhar(8000)
    DECLARE @Res varchar(100)
    SELECT @SQL = 'Select [@Res] ' + @CurCounterName
    + ' FROM Client WHERE ClientID=' + '''' + @CurClientID + ''''

    exec (@ExecString)
    It works, but after executing @Res = NULL...

  11. #11
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621
    Originally posted by yurich
    It works! Thanks a lot. Now I need to get out result of this query to local variable:

    DECLARE @SQL varvhar(8000)
    DECLARE @Res varchar(100)
    SELECT @SQL = 'Select [@Res] ' + @CurCounterName
    + ' FROM Client WHERE ClientID=' + '''' + @CurClientID + ''''

    exec (@ExecString)
    It works, but after executing @Res = NULL...
    You cannot save result to local variable but you could save it to temporary table:

    create table #tmp(...)
    insert #tmp
    exec(....)

Posting Permissions

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