none
dynamic query

    Question

  • Hi List,

    I am using dynamic query, but unable to set a declared value as it does not like it while using dynamic query. Here is what I am trying to do:

    DECLARE @sqlCommand nvarchar(1000) DECLARE @tablename nvarchar(75) DECLARE @city nvarchar(75) SET @tablename= 'dbo.mytable' SET @sqlCommand = 'SELECT ' + @city + ' = city FROM ' + @tablename + ' WHERE City ='london ' EXEC (@sqlCommand)

    when I run this, it does not allocate 'london' to @city, the @city is empty. If I run a static query, that is fine. But I have to use dynamic query. Any solution? I was thinking of writing a function to return the @city, but there must be an easier approach.

    thanks

    Arvin

    Thursday, January 23, 2014 9:58 AM

Answers

  • DECLARE @sqlCommand nvarchar(1000)
    DECLARE @tablename nvarchar(75)
    DECLARE @city nvarchar(75)
    SET @tablename= '[Person].[CountryRegion]'
    declare @cityret varchar(75);
    
    declare @ParmDefinition nvarchar(50);
    SET @sqlCommand = 'SELECT  @city  = [name] FROM ' + @tablename + ' WHERE [Name] =''United States'''
    
    
    SET @ParmDefinition = N'@city varchar(50) OUTPUT';
    
    EXEC sp_executesql @SQLcommand, @ParmDefinition, @city=@cityret OUTPUT;
    
    SELECT @cityret;
    In here the value for the city will be saved into @cityret

    Satheesh
    My Blog


    • Proposed as answer by Latheesh NKMVP Thursday, January 23, 2014 11:24 AM
    • Marked as answer by azolfaghari Thursday, January 23, 2014 12:19 PM
    Thursday, January 23, 2014 10:34 AM
  • Thanks all, 
    the last solution provided by Satheesh was the right one. I gave you a vode to Papy for your effort.

    Here is the updated code:


    CREATE TABLE dbo.CityTable
    ( CityId INT IDENTITY(1,1) NOT NULL
     CONSTRAINT  PK_CityTable_CityId PRIMARY KEY,
      CityName NVARCHAR(200) NOT NULL ,
      Description NVARCHAR(500) NOT NULL)
    GO
    INSERT INTO dbo.CityTable(CityName,Description) VALUES
    ('New York','The town where Manhattan is situated'),
    ('London' , 'The heart of the United Kingdom'),
    ('Edinburgh', 'Famous for Arthurs Seat');


    DECLARE @sqlCommand nvarchar(max)
    DECLARE @tablename nvarchar(75)
    DECLARE @city nvarchar(75)
    SET @tablename= 'dbo.CityTable'
    declare @cityret nvarchar(75);
    declare @ParmDefinition nvarchar(50);
    SET @sqlCommand = 'SELECT  @city  = [CityName] FROM ' + @tablename + ' WHERE [CityName] =''london'''

    SET @ParmDefinition = N'@city varchar(50) OUTPUT';
    EXEC sp_executesql @SQLcommand, @ParmDefinition, @city=@cityret OUTPUT;
    SELECT @cityret;

    --TO test it to see it works
    SET @sqlCommand = 'SELECT  * FROM ' + @tablename + ' WHERE [CityName] =''' + @cityret+ ''''
    EXEC sp_executesql @sqlCommand

    • Marked as answer by azolfaghari Thursday, January 23, 2014 12:22 PM
    Thursday, January 23, 2014 12:22 PM

All replies

  • You can use OUTPUT Parameter or display the value within the scope of dynamic SQL as below:

    [Note: The below is just a sample code.You may change it as per your requirement]

    Try something below:

    Create Table dbo.mytable (country varchar(100), City varchar(100))
    Insert into dbo.MyTable Select 'USA','NewYork'
    
    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @tablename nvarchar(75)
    
    SET @tablename= 'dbo.mytable'
    
    SET @sqlCommand = 'DECLARE @city nvarchar(75);Set @city = (SELECT  city FROM ' + @tablename + ' WHERE country = ''USA''); Select @City'
    Print @sqlCommand
    EXEC (@sqlCommand)
    
    Drop table dbo.mytable

    Thursday, January 23, 2014 10:08 AM
  • Example
    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @tablename nvarchar(75)
    DECLARE @city nvarchar(75)
    SET @tablename= '[Person].[CountryRegion]'
    declare @cityret varchar(75);
    
    declare @ParmDefinition nvarchar(50);
    SET @sqlCommand = 'SELECT  @city  = [name] FROM ' + @tablename + ' WHERE [Name] =''United States'''
    
    
    SET @ParmDefinition = N'@city varchar(50) OUTPUT';
    
    EXEC sp_executesql @SQLcommand, @ParmDefinition, @city=@cityret OUTPUT;
    
    SELECT @cityret;


    Satheesh
    My Blog


    Thursday, January 23, 2014 10:10 AM
  • Try sp_executesql

    http://technet.microsoft.com/en-us/library/ms188001.aspx

    http://technet.microsoft.com/en-us/library/ms175170(v=sql.105).aspx

    Mark as ans if you find it useful

    Thursday, January 23, 2014 10:27 AM
  • sorry I forgot to mention that during my query I am going to call @city more than one time. So the second solution provided, is not working for me. Because right after dynamic query, @city does not exist any more.

    Is parmDefinition doing what I am after?

    thanks

    Arvin

    Thursday, January 23, 2014 10:32 AM
  • DECLARE @sqlCommand nvarchar(1000)
    DECLARE @tablename nvarchar(75)
    DECLARE @city nvarchar(75)
    SET @tablename= '[Person].[CountryRegion]'
    declare @cityret varchar(75);
    
    declare @ParmDefinition nvarchar(50);
    SET @sqlCommand = 'SELECT  @city  = [name] FROM ' + @tablename + ' WHERE [Name] =''United States'''
    
    
    SET @ParmDefinition = N'@city varchar(50) OUTPUT';
    
    EXEC sp_executesql @SQLcommand, @ParmDefinition, @city=@cityret OUTPUT;
    
    SELECT @cityret;
    In here the value for the city will be saved into @cityret

    Satheesh
    My Blog


    • Proposed as answer by Latheesh NKMVP Thursday, January 23, 2014 11:24 AM
    • Marked as answer by azolfaghari Thursday, January 23, 2014 12:19 PM
    Thursday, January 23, 2014 10:34 AM
  • Hello ,

    Your code is false

    I have tested this code

    and I have tested this code in the query window of SSMS :

    USE MyTestDb
    GO
    CREATE TABLE dbo.CityTable
    ( CityId INT IDENTITY(1,1) NOT NULL
     CONSTRAINT  PK_CityTable_CityId PRIMARY KEY,
      CityName NVARCHAR(200) NOT NULL ,
      Description NVARCHAR(500) NOT NULL)
    GO
    INSERT INTO dbo.CityTable(CityName,Description) VALUES
    ('New York','The town where Manhattan is situated'),
    ('London' , 'The heart of the United Kingdom'),
    ('Edinburgh', 'Famous for Arthurs Seat');
    GO
    SELECT * FROM dbo.CityTable;
    GO
    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @tablename nvarchar(75)
    DECLARE @city nvarchar(75)
    SET @tablename= 'dbo.mytable'
    
    SET @sqlCommand = 'SELECT ' + @city + ' = city FROM ' + @tablename + ' WHERE City ='london '
    EXEC (@sqlCommand
    DROP TABLE dbo.CityTable;
    GO

    I have obtained the following error message :

    "unclosed quotation mark.... "

    I am not sure to have understood what you want to do as you have forgotten to provide the CREATE TABLE statement but I would suggest you to provide it and the full tested dynamic statement you want to create. Maybe I am going wrong , but I have understood you want to return the same value from the SELECT as you have provided in the WHERE clause.

    With my own table definition :

    SELECT DESCRIPTION FROM dbo.CityTable

    As It is possible that a problem of dynamic statement build , it is possible that a moderator moves your thread where a more suitable forum.

    We are waiting for your feedback to try to help you more efficiently.

    Have a nice day

    PS : I think  that the following code is working :

    DECLARE @sqlCommand nvarchar(1000)
    DECLARE @tablename nvarchar(75)
    DECLARE @city AS NVARCHAR(75)
    SET @tablename= 'dbo.CityTable'
    SET @city = 'CityName'
    
    SET @sqlCommand = 'SELECT ' + @city + '  FROM ' + @tablename + ' WHERE CityName =''london'' ';
    EXEC (@sqlCommand);
    

    Try to insert before the EXEC statement the  following statement

    select @sqlcommand

    you will be able to check whether your statement is correct


    Mark Post as helpful if it provides any help.Otherwise,leave it as it is.


    Thursday, January 23, 2014 11:19 AM
    Moderator
  • Thanks all, 
    the last solution provided by Satheesh was the right one. I gave you a vode to Papy for your effort.

    Here is the updated code:


    CREATE TABLE dbo.CityTable
    ( CityId INT IDENTITY(1,1) NOT NULL
     CONSTRAINT  PK_CityTable_CityId PRIMARY KEY,
      CityName NVARCHAR(200) NOT NULL ,
      Description NVARCHAR(500) NOT NULL)
    GO
    INSERT INTO dbo.CityTable(CityName,Description) VALUES
    ('New York','The town where Manhattan is situated'),
    ('London' , 'The heart of the United Kingdom'),
    ('Edinburgh', 'Famous for Arthurs Seat');


    DECLARE @sqlCommand nvarchar(max)
    DECLARE @tablename nvarchar(75)
    DECLARE @city nvarchar(75)
    SET @tablename= 'dbo.CityTable'
    declare @cityret nvarchar(75);
    declare @ParmDefinition nvarchar(50);
    SET @sqlCommand = 'SELECT  @city  = [CityName] FROM ' + @tablename + ' WHERE [CityName] =''london'''

    SET @ParmDefinition = N'@city varchar(50) OUTPUT';
    EXEC sp_executesql @SQLcommand, @ParmDefinition, @city=@cityret OUTPUT;
    SELECT @cityret;

    --TO test it to see it works
    SET @sqlCommand = 'SELECT  * FROM ' + @tablename + ' WHERE [CityName] =''' + @cityret+ ''''
    EXEC sp_executesql @sqlCommand

    • Marked as answer by azolfaghari Thursday, January 23, 2014 12:22 PM
    Thursday, January 23, 2014 12:22 PM