Post reply

Passing a Constant in a query which is stored in a variable.

  • May 30, 2011 at 7:23 am

    #389618

    Dear Forum members,

    I am trying to capture the stats of dataware house. So I use a query which is stored in a variable. Now I am in need of getting a constant inside that query which is stored in a variable. How to do it ...

    declare @column nvarchar(max)

    declare @sql1 nvarchar(max)

    declare @constant1 nvarchar(max)

    declare @constant2 nvarchar(max)

    set @constant1 = 'SAP'

    set @constant2 = 'Infinium'

    --declare @res int

    set @sql1 = 'SELECT COUNT(*) FROM "HRDATAMART_DAILY"."dbo"."EMPLOYEE" WHERE EMP_MOST_RECT_HIRE_DT IS NULL AND SOURCE_NM = '+@constant1+''

    Select @sql1

    The output of the query will be

    SELECT COUNT(*) FROM "HRDATAMART_DAILY"."dbo"."EMPLOYEE" WHERE EMP_MOST_RECT_HIRE_DT IS NULL AND SOURCE_NM = SAP

    But i need the output as 'SAP' in the where condition (i.e with single quotes)

    The exact required code would be this :

    SELECT COUNT(*) FROM "HRDATAMART_DAILY"."dbo"."EMPLOYEE" WHERE EMP_MOST_RECT_HIRE_DT IS NULL AND SOURCE_NM = 'SAP'

    Please help me on this. Thanks in advance....

  • May 30, 2011 at 7:38 am

    #1331623

    set @sql1 = 'SELECT COUNT(*) FROM "HRDATAMART_DAILY"."dbo"."EMPLOYEE" WHERE EMP_MOST_RECT_HIRE_DT IS NULL AND SOURCE_NM = '''+@constant1+''''

    🙂

  • May 30, 2011 at 7:45 am

    #1331629

    Why not just do select * FROM ... WHERE Column = @Variable

    You won't need quotes for that.

    Moreover you won't need dynamic sql!

  • May 30, 2011 at 8:18 am

    #1331644

    Thanks a lot Victor ... Cheers 🙂

  • May 30, 2011 at 8:20 am

    #1331646

    thanks Ninja !

  • May 30, 2011 at 8:20 am

    #1331647

    shyamhr (5/30/2011)

    Thanks a lot Victor ... Cheers 🙂

    Seriously why do you need dynamic sql here??? I really don't see the need for it!

  • May 30, 2011 at 8:25 am

    #1331648

    Ninja,

    I have shown only a part of a code, I actually pass two variables. One table name and other column name. The aim of my dynamic SQL is to get all stats like null count, distinct count etc ... from columns of each and every table in DB. thats y ...

  • May 30, 2011 at 9:35 am

    #1331664

    This won't answer your question directly but your requirement is very similar to something I have done.

    Here is what I did for that.

    http://jasonbrimhall.info/2011/02/08/t-sql-tuesday-15-dba-automaton/

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

Viewing 8 posts - 1 through 7 (of 7 total)

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