dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] SQL - Issue with local variable in a select query

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Resolved [RESOLVED] SQL - Issue with local variable in a select query

    Hi
    I am preparing some dynamic stored procedures and while doing that am trying to use a variable in my query but I get an syntax error which I cannot figure out why and how:

    Code:
    BEGIN
    DECLARE @cols  AS NVARCHAR(MAX)='';
    DECLARE @query AS NVARCHAR(MAX)='';
    DECLARE @WhereInit AS NVARCHAR(MAX)= ' Where Emp_ID in (''ARML'',''TH'',''CBDI'')';
    
    SELECT @cols = @cols + QUOTENAME(Emp_ID) + ',' 
    FROM (SELECT distinct Emp_ID FROM CM_DMS @WhereInit ) AS ThisTable
    
    SELECT @cols = substring(@cols, 0, len(@cols))
    
    Set @query = 
    'Select * from
    ( 
     SELECT CM_JFIM, module_code as ModuleCode, Course_code AS CourseCode, course_description As DocTitle, COURSE_FinalStatus, Emp_ID 
     from CM_DMS  
     '+ @WhereInit +'
    ) Origin
    pivot 
    (
     max(COURSE_FinalStatus) for Emp_ID in (' + @cols + ')
    ) piv_Table'
    
    execute(@query)
    END
    The thing is underlined and when I run the query it tells me "Incorrect syntax near '@WhereInit'."

    Why am I getting this error?

    Edit: of course that was not how variables work. Corrected that but now I get this error "Incorrect syntax near ')'.":

    Code:
    BEGIN
    DECLARE @cols  AS NVARCHAR(MAX)='';
    DECLARE @query AS NVARCHAR(MAX)='';
    DECLARE @WhereInit AS NVARCHAR(MAX)= 'Where Emp_ID in (''ARML'',''TH'',''CBDI'')';
    DECLARE @WhereCols AS NVARCHAR(MAX)= '''ARML'',''TH'',''CBDI''';
    
    SELECT @cols = @cols + QUOTENAME(Emp_ID) + ',' 
    FROM (SELECT distinct Emp_ID FROM CM_DMS Where Emp_ID in (@WhereCols)) AS ThisTable
    SELECT @cols = substring(@cols, 0, len(@cols))
    
    Set @query = 
    'Select * from
    ( 
     SELECT CM_JFIM, module_code as ModuleCode, Course_code AS CourseCode, course_description As DocTitle, COURSE_FinalStatus, Emp_ID 
     from CM_DMS  
     '+ @WhereInit +'
    ) Origin
    pivot 
    (
     max(COURSE_FinalStatus) for Emp_ID in (' + @cols + ')
    ) piv_Table'
    
    execute(@query)
    END
    What is wrong with it now?

    Thanks for any help.
    Last edited by Grand; Nov 27th, 2019 at 02:05 PM.

  2. #2
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,563

    Re: SQL - Issue with local variable in a select query

    Code:
    ...Where Emp_ID in (@WhereCols)) A...
    You have one too many ) there....

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Issue with local variable in a select query

    Hi
    Not when looking at the whole query though:
    Code:
    (SELECT distinct Emp_ID FROM CM_DMS Where Emp_ID in (@WhereCols))
    I think it has something to do with the apostrophes in this? :
    Code:
    DECLARE @WhereCols AS NVARCHAR(MAX)= '''ARML'',''TH'',''CBDI'''
    Last edited by Grand; Nov 27th, 2019 at 02:48 PM.

  4. #4
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,563

    Re: SQL - Issue with local variable in a select query

    Right you are...

    I think you're going to need to print out @query at the end there, instead of executing it... and looking at just what exactly is being built. Once you do that, hopefully you'll see what the issue is. (Hint, you end up with an extra , at the end of something that shouldn't have an , at the end.)

    I started to question what you were doing... then I realized that it's a dynamic pivot query... ick. I haven't written one of those in a long time. I don't remember it being this messy, but it might have been.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Issue with local variable in a select query

    Thanks for the hint. The query being messy is my fault
    Printing reviled that @cols is empty.
    This gives me the result:

    Code:
    SELECT @cols = @cols + QUOTENAME(Emp_ID) + ',' 
    FROM (SELECT distinct Emp_ID FROM CM_DMS Where Emp_ID in ('ARML','TH','CBDI')) AS ThisTable
    
    SELECT @cols = substring(@cols, 0, len(@cols))
    but this one doesn't:
    Code:
    DECLARE @WhereCols AS NVARCHAR(MAX)= '''ARML'',''TH'',''CBDI'''
    
    SELECT @cols = @cols + QUOTENAME(Emp_ID) + ',' 
    FROM (SELECT distinct Emp_ID FROM CM_DMS Where Emp_ID in (@WhereCols)) AS ThisTable
    
    SELECT @cols = substring(@cols, 0, len(@cols))
    I cannot figure out how to construct this: '''ARML'',''TH'',''CBDI''' to fit in where in parameter. I am going to pass it as a parameter from client side when executing sql command.

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,583

    Re: SQL - Issue with local variable in a select query

    No this will not work.
    The IN in "dynamic" does not work in that way.
    I'm trying to find my thread that I was posting a while back that had the solution, so just bear with me...
    Last edited by sapator; Nov 28th, 2019 at 10:34 AM.
    Slow as hell.

  7. #7
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,583

    Re: SQL - Issue with local variable in a select query

    Got it.
    SO:
    DECLARE @WhereCols AS NVARCHAR(MAX)= '''ARML'',''TH'',''CBDI''' etc...
    (SELECT distinct Emp_ID FROM CM_DMS Where','+@WhereCols +',' LIKE '%,'+[Emp_ID ]+',%') ...etc
    Slow as hell.

  8. #8
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,563

    Re: SQL - Issue with local variable in a select query

    For situations like this, I create a table var, split the string up, and put each value in it... then you can do things like this:
    Code:
    Where myField in (select fld from @tempTbl)
    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Issue with local variable in a select query

    Quote Originally Posted by sapator View Post
    Got it.
    SO:
    DECLARE @WhereCols AS NVARCHAR(MAX)= '''ARML'',''TH'',''CBDI''' etc...
    (SELECT distinct Emp_ID FROM CM_DMS Where','+@WhereCols +',' LIKE '%,'+[Emp_ID ]+',%') ...etc
    Thanks.
    I have tried it but it gives me this error:
    "An expression of non-boolean type specified in a context where a condition is expected, near ')'."
    It does not seem right to just write this for where clause: FROM CM_DMS Where ','+

  10. #10
    Super Moderator FunkyDexter's Avatar
    Join Date
    Apr 2005
    Location
    An obscure body in the SK system. The inhabitants call it Earth
    Posts
    7,369

    Re: SQL - Issue with local variable in a select query

    You can't just reference a variable directly in a sql statement like that. You need to concatenate it in the same way as you have in the subsequent query:-

    Code:
    SELECT @cols = @cols + QUOTENAME(Emp_ID) + ',' 
    FROM (SELECT distinct Emp_ID FROM CM_DMS ' + @WhereInit + ') AS ThisTable'
    Even better would be to use sp_ExecuteSQL. Using that you can reference the variable without concatenation, much as you did, but you pass the value to be substituted as a parameter. This protects you against injection attacks.

    Honestly, though, I'm with TG ad I think you'd be better revisiting your approach. I'm guessing that @WhereInit is ultimately intended to be a parameter to the sproc rather than an internal variable as it is now (otherwise, why would make it a variable). The best way of dealing with dynamic in lists like that is to pass them in as a comma separated list, use something like String_Split to flip it into a table and then join to the result. This avoids the need to use concatenation at all.
    You can depend upon the Americans to do the right thing. But only after they have exhausted every other possibility - Winston Churchill

    Hadoop actually sounds more like the way they greet each other in Yorkshire - Inferrd

  11. #11
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    5,583

    Re: SQL - Issue with local variable in a select query

    Just to clarify. I would also be using either a temp table or a table var.
    I was just answering to the specific question.

    Now. What you must do and it will work is this:

    Code:
    DECLARE @WhereCols AS NVARCHAR(MAX)
    
    set @WhereCols =   'ARML,TH,CBDI'
    
    SELECT distinct Emp_ID FROM CM_DMS Where ','+@WhereCols +',' LIKE '%,'+[Emp_ID ]+',%'
    Last edited by sapator; Nov 29th, 2019 at 05:56 AM. Reason: metaaaalllll!
    Slow as hell.

  12. #12
    PowerPoster techgnome's Avatar
    Join Date
    May 2002
    Posts
    32,563

    Re: SQL - Issue with local variable in a select query

    Hmmmm... New day, new thought process and .... It just dawned on me what he's trying to do with that where clause...
    Looking back at the original code:
    Code:
    DECLARE @WhereInit AS NVARCHAR(MAX)= ' Where Emp_ID in (''ARML'',''TH'',''CBDI'')';
    @Grand - ARML , TR, and CBDI .... what ARE those? Are they fields in your table? Are you allowing the user to select which columns to look in for the Emp_Id? OR are those actual Emp_Ids? (If they are, they seem like weird ones to me, but what ever.) . If they are columns like I suspect they are, then this whole approach is wrong and needs to be completely rethought out from the start.


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Issue with local variable in a select query

    Quote Originally Posted by sapator View Post
    Just to clarify. I would also be using either a temp table or a table var.
    I was just answering to the specific question.

    Now. What you must do and it will work is this:

    Code:
    DECLARE @WhereCols AS NVARCHAR(MAX)
    
    set @WhereCols =   'ARML,TH,CBDI'
    
    SELECT distinct Emp_ID FROM CM_DMS Where ','+@WhereCols +',' LIKE '%,'+[Emp_ID ]+',%'
    This worked, thank you

    Code:
    BEGIN
    DECLARE @cols  AS NVARCHAR(MAX)='';
    DECLARE @query AS NVARCHAR(MAX)='';
    DECLARE @WhereInit AS NVARCHAR(MAX)= 'Where Emp_ID in (''ARML'',''TH'',''CBDI'')';
    DECLARE @WhereCols AS NVARCHAR(MAX) = 'ARML,TH,CBDI'
    
    SELECT @cols = @cols + QUOTENAME(Emp_ID) + ',' 
    FROM (SELECT distinct Emp_ID FROM CM_DMS Where ','+@WhereCols +',' LIKE '%,'+Emp_ID+',%')AS ThisTable
    
    SELECT @cols = substring(@cols, 0, len(@cols))
    
    Set @query = 
    'Select * from
    ( 
     SELECT CM_JFIM, module_code as ModuleCode, Course_code AS CourseCode, course_description As DocTitle, COURSE_FinalStatus, Emp_ID 
     from CM_DMS  
     '+ @WhereInit +'
    ) Origin
    pivot 
    (
     max(COURSE_FinalStatus) for Emp_ID in ('+@cols+')
    ) piv_Table'
    
    exec(@query)
    END
    I am thinking about the other approach and want to define a user-defined type in the database and use that when running the query. I am doing a similar thing with a table which I pass from application on client side to database.
    I mark this thread as resolved.

    Thanks a lot again. I really appreciate this as the whole simplification of tables structure was hanging on this.

  14. #14

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: SQL - Issue with local variable in a select query

    Quote Originally Posted by techgnome View Post
    Hmmmm... New day, new thought process and .... It just dawned on me what he's trying to do with that where clause...
    Looking back at the original code:
    Code:
    DECLARE @WhereInit AS NVARCHAR(MAX)= ' Where Emp_ID in (''ARML'',''TH'',''CBDI'')';
    @Grand - ARML , TR, and CBDI .... what ARE those? Are they fields in your table? Are you allowing the user to select which columns to look in for the Emp_Id? OR are those actual Emp_Ids? (If they are, they seem like weird ones to me, but what ever.) . If they are columns like I suspect they are, then this whole approach is wrong and needs to be completely rethought out from the start.


    -tg
    Hi
    Thanks for looking into this again. Yes, they are columns (fields) and they can vary based on user selections. They are actually values in column Emp_ID of CM_DMS table which then are pivoted to fields.

    Do you have a more elegant approach in mind? I must say that the efficiency is an issue as for now

  15. #15

    Thread Starter
    Hyperactive Member
    Join Date
    Nov 2018
    Posts
    383

    Re: [RESOLVED] SQL - Issue with local variable in a select query

    OK, I just realised that I can achieve the same thing just by building my string much much easier at client side and run it a sql query:
    Code:
    Select * from
    ( 
    SELECT CM_JFIM As JFIM, module_code as ModuleCode, Course_code AS CourseCode, course_description As DocTitle, COURSE_FinalStatus, Emp_ID 
    from CM_DMS  
    Where Emp_ID in ('ARML','TH','CBDI')
    ) Origin
    pivot 
    (
    max(COURSE_FinalStatus) for Emp_ID in (ARML,TH,CBDI)
    ) piv_Table
    Would this give any performance issue?

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width