Results 1 to 15 of 15
  1. #1
    Join Date
    Mar 2009
    Posts
    47

    Unanswered: Query in Stored procedure

    Hi,
    See if u could help me with the following...
    How I can retrieve value returned by Query into a variable...

    e.g If I want name of Customer in a variable whose account no is 5

    I have written
    set @Name=select Name from Customers where AccountNo=5
    It doesn't work it gives error...
    Also I want to do this stored proc. bcoz futher I need tht variable @Name for some other Query...
    Waiting for some ans.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Wrap ur query up in parentheses.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2009
    Location
    India
    Posts
    2
    Hello,

    Try this

    SELECT @Name = Name
    FROM Customers
    WHERE AccountNo = 5

    Hope helpful...

  4. #4
    Join Date
    Jan 2009
    Location
    United Kingdom
    Posts
    77
    DECLARE @name VARCHAR(100)
    set @Name=(select Name from Customers where AccountNo=5)

  5. #5
    Join Date
    Mar 2009
    Posts
    47
    I want to use it in stored proc.
    where I am trying to pass tablename as a parameter..
    Wht I am doing is:
    select @Name=('select Name from '+@TableName+'where Accid = '+@AccId)
    It doesn't return the value of @Name but it returns 'select Name'

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    That's a totally different problem to your initial one. Is there any more to this? Do you seriously have loads of tables where you store account details? This is very likely a design error. You should also look up SQL Injection - you are opening up a serious security vulnerability.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Mar 2009
    Posts
    47

    Question

    Hi I want to use the select query in stored proc..
    Plz tell me wht is wrong with the following statement if I use it in a stored proc.

    select @Name=('select Name from '+@TableName+'where Accid = '+@AccId)

    here Tablename refers to the name of the table passed...
    In the above statement I tried using Exec which we generally do when tablename is passed as a parameter...

    select @Name=exec(('select Name from '+@TableName+'where Accid = '+@AccId)) this also doesn't work

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    The EXEC code executes in a different scope to the rest of the procedure. This is not trivial to do. It is also a Big Red Flag that this is not something you should blindly be doing as, to repeat myself, this smacks of poor design and poorer practice.

    Look up sp_executesql - you can use that to return the value of parameters back to your calling code.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Mar 2009
    Posts
    47

    Question

    Hi tried using sp_executesql but again there is some problem The code is as follows

    BEGIN
    DECLARE @SQLSTRING NVARCHAR(500)
    DECLARE @PARMDEFINITION NVARCHAR(500)
    DECLARE @INTVARIABLE INT
    DECLARE @NAME VARCHAR(30)
    DECLARE @XYZ VARCHAR(30)

    SET @SQLSTRING=N'SELECT @NAMEOUT=NAME FROM @TABLENAME WHERE AccID=@AccID'
    SET @PARMDEFINITION=N'@TABLENAME VARCHAR(30),@AccID INT,
    @NAMEOUT VARCHAR(30) OUTPUT'
    SET @INTVARIABLE=1
    SET @XYZ='CustomerMASTER'

    EXECUTE SP_EXECUTESQL
    @SQLSTRING,
    @PARMDEFINITION,
    @AccID=@INTVARIABLE,
    @TABLENAME=@XYZ,
    @NAMEOUT=@NAME OUTPUT
    SELECT @NAME
    END
    Last edited by SnehaAgrawal; 04-09-09 at 07:29.

  10. #10
    Join Date
    Mar 2009
    Posts
    47

    Question

    Hi tried using sp_executesql but again there is some problem The code is as follows

    BEGIN
    DECLARE @SQLSTRING NVARCHAR(500)
    DECLARE @PARMDEFINITION NVARCHAR(500)
    DECLARE @INTVARIABLE INT
    DECLARE @NAME VARCHAR(30)
    DECLARE @XYZ VARCHAR(30)

    SET @SQLSTRING=N'SELECT @NAMEOUT=NAME FROM @TABLENAME WHERE AccID=@AccID'
    SET @PARMDEFINITION=N'@TABLENAME VARCHAR(30),@AccID INT,
    @NAMEOUT VARCHAR(30) OUTPUT'
    SET @INTVARIABLE=1
    SET @XYZ='CustomerMASTER'

    EXECUTE SP_EXECUTESQL
    @SQLSTRING,
    @PARMDEFINITION,
    @AccID=@INTVARIABLE,
    @TABLENAME=@XYZ,
    @NAMEOUT=@NAME OUTPUT
    SELECT @NAME
    END

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You cannot use a tablename as a parameter. You will have to concatenate the value of @tablename into your SQL statement. The reason? Because it is not supposed to be configurable at run time.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Mar 2009
    Posts
    47
    How to concatenate the value of @Tablename?Plz guide me...I mean just send the above query modified by concatenating @tablename..

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Exactly as you did in post #7
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Mar 2009
    Posts
    47
    thnks for ur help

  15. #15
    Join Date
    Nov 2004
    Location
    Canada
    Posts
    58
    Use pubs
    go
    Declare @name varchar(30)
    Declare @tablename varchar(50)
    Declare @Sql varchar(5000)

    Set @Name = (select distinct city from Authors where City like 'BE%')

    set @tableName = 'publishers'

    Set @SQL ='Select * from ' + @TableName + ' where city = ' + '''' + @Name+ ''''


    Print (@sql)
    Execute (@sql)

Posting Permissions

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