dynamic query..Help needed pls

  • SET @OrgName = N'SELECT ORG_ID ' + CONVERT(varchar(10), @orgid) +' '+ '[Description]' + ' ' +'('+ CONVERT(varchar(20),+ 'pol_id)'

    +')'+ 'from'+ @dbname + '.dbo.Organization'+ 'where OrganizationID =' + @orgid;

    Above is the dynamic query i wrote in my Sql mgmt studio and i ran it to get the following error. Actually this script is a part of my program that uses cursors concept

    Error:

    Msg 102, Level 15, State 1, Line 53

    Incorrect syntax near ';'.

    --Pra:-):-)--------------------------------------------------------------------------------

  • prathibha_aviator (1/22/2013)


    SET @OrgName = N'SELECT ORG_ID ' + CONVERT(varchar(10), @orgid) +' '+ '[Description]' + ' ' +'('+ CONVERT(varchar(20),+ 'pol_id)'

    +')'+ 'from'+ @dbname + '.dbo.Organization'+ 'where OrganizationID =' + @orgid;

    Above is the dynamic i wrote in my Sql mgmt studio and i ran it to get the following error. Actually this script is a part of my program that uses cursors concept

    Error:

    Msg 102, Level 15, State 1, Line 53

    Incorrect syntax near ';'.

    I haven't actually tried this in SSMS so my answer may be off. The first thing I would try is to convert the @orgid at the end to a varchar as was done earlier. There may be a problem concatenating it at the end of the string without converting.

    Good luck!

  • Talk about sparse details. It would be helpful if in the future you could provide more details. At the very least declare the variables.

    Once I declared the variables so I could start looking it was pretty obvious.

    declare @OrgName varchar(max), @OrgID int, @dbname varchar(50)

    Then just start removing stuff from your code to isolate the issue.

    The culprit is here.

    CONVERT(varchar(20),+ 'pol_id)'

    That will get rid of the syntax error but...

    Actually this script is a part of my program that uses cursors concept

    Cursors are horribly slow and should be used in certain situations. I can't tell from your snippet but I doubt that what you doing here is one of those situations. If you want to really improve your code, take a look at the first link in my signature about best practices, post ddl and sample data along with desired output and we can help you get rid of that nasty cursor.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Sean, Have a look at my complete code

    USE msdb

    declare @dbname varchar(50);

    declare @SQL nvarchar(MAX)='';

    declare @AsOfDate datetime = convert(datetime, '12/31/2012');

    declare @orgid int = 0;

    declare @assets money = 0.00;

    declare @liabilities money = 0.00;

    declare @netassets money = 0.00;

    declare @income money = 0.00;

    declare @expense money = 0.00;

    declare @dedicated money = 0.00;

    declare @Off money = 0.00;

    declare @OrgName varchar(50);

    DECLARE @dbloop CURSOR;

    SET @dbloop= CURSOR

    FOR

    select name from sys.databases where name NOT IN ('master','model','msdb','tempdb')

    AND State_desc = 'ONLINE'

    DECLARE Parishes_CURSOR CURSOR

    FAST_FORWARD

    READ_ONLY

    FOR

    select OrganizationID from Organization where IsHostOrganization <> 1

    OPEN @dbloop

    fetch next from @dbloop into @dbname

    while (@@FETCH_STATUS <>0)

    BEGIN

    IF OBJECT_ID(@DBName + N'.dbo.Account') IS NOT NULL

    BEGIN

    OPEN Parishes_CURSOR

    FETCH NEXT FROM Parishes_CURSOR INTO @OrgID

    WHILE (@@FETCH_STATUS <> -1)

    BEGIN

    IF (@@FETCH_STATUS <> -2)

    BEGIN

    IF ( @@FETCH_STATUS <> 0 )

    BREAK

    SET @OrgName = N'SELECT ORG_ID ' + CONVERT(varchar(10), @orgid) +' '+ '[Description]' + ' ' +'('+ CONVERT(varchar(20),+ 'pol_id)'

    +')'+ 'from'+ @dbname + '.dbo.Organization'+ 'where'+ 'OrganizationID' +'='+ orgid;

    drop table #AcctBal;

    create table #AcctBal(AccountID int, Balance money);

    insert #AcctBal(AccountID, Balance)

    exec resultset_AccountEndingBalance @orgid,@AsOfDate;

    SET @assets= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +1+ 'and ' +'RecordStatus'+ '='+ '1';

    SET @liabilities = N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account'+ 'ON' +'#AcctBal.AccountID'+' =' +' Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +2+ 'and ' +'RecordStatus'+ '='+ '1';

    SET @netassets= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +3+ 'and ' +'RecordStatus'+ '='+ '1';

    SET @income= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +4+ 'and ' +'RecordStatus'+ '='+ '1';

    SET @expense= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +5+ 'and ' +'RecordStatus'+ '='+ '1';

    SET @dedicated= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +6+ 'and ' +'RecordStatus'+ '='+ '1';

    if (@dedicated is null)

    set @dedicated = 0.00;

    --print 'Assets ' + Convert(varchar(20), @assets);

    --print 'Liabilities ' + Convert(varchar(20), @liabilities);

    --print 'Net Assets ' + Convert(varchar(20), @netassets);

    --print 'Income ' + Convert(varchar(20), @income);

    --print 'Expense ' + Convert(varchar(20), @expense);

    --print 'Current Period Change ' + Convert(varchar(20), (@income - @expense));

    --print 'Dedicated ' + Convert(varchar(20), @dedicated);

    set @Off = @assets - (@liabilities + @netassets + @dedicated + (@income - @expense));

    if (@Off <> 0.00)

    SET @SQL= @SQL + @OrgName + ' is out of balance by ' + convert(varchar(30), @Off) + CHAR(13) + CHAR(10) ;

    print @SQL;

    END

    FETCH NEXT FROM Parishes_CURSOR INTO @OrgID

    END

    CLOSE Parishes_CURSOR

    DEALLOCATE Parishes_CURSOR

    End

    fetch next from @dbloop into @dbname

    END

    CLOSE @dbloop

    deallocate @dbloop

    --Pra:-):-)--------------------------------------------------------------------------------

  • The culprit is here.

    CONVERT(varchar(20),+ 'pol_id)'

    Exactly, it says the same.. But I couldn't find where it is.. Am i supposed to write the whole convert function dynamically???

    --Pra:-):-)--------------------------------------------------------------------------------

  • prathibha_aviator (1/22/2013)


    The culprit is here.

    CONVERT(varchar(20),+ 'pol_id)'

    Exactly, it says the same.. But I couldn't find where it is.. Am i supposed to write the whole convert function dynamically???

    Yes, you can't write part of a function. The syntax is all messed up on that.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • prathibha_aviator (1/22/2013)


    Sean, Have a look at my complete code

    I am going to guess that you didn't read that article. It certainly is somewhat of a help to see all of the code but without any table definitions it is pretty hard to do much here.

    You do probably have justification for using a cursor since you are looping through a series of databases. It is generally not a good idea to stick business logic in MSDB which you done here. I would recommend a database for this type of thing instead of tacking stuff onto the side of MSDB. Create a Common, or Utilities or something like that.

    Also you can greatly streamline this by not using a separate query for each piece of aggregate data.

    I will write up a simpler version of this in a bit and see what you think.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Is this what you wanted, I doubt you'd need ORG_ID value twice there..

    SET @OrgName = 'SELECT ORG_ID+''' + CONVERT(varchar(10), @orgid) +''' '+ '+[Description]+' + ' ' +'''('''+ + '+CONVERT(varchar(20),pol_id)'

    +'+'')'''+ ' from '+ @dbname + '.dbo.Organization'+ ' where OrganizationID =' + convert(varchar(10),@orgid)

  • No offense but this code is a disaster. You have 2 cursors and the declarations are completely different, the check for fetch_status are overly complicated and not documented. Do you know what a fetch_status of -1 means? How about -2? Me neither. Does it really matter? Anything other than 0 means it failed and your code bails out.

    What does resultset_AccountEndingBalance do?

    You have tons of dynamic sql generated but you never execute any of it, as a result all of your variables will always be NULL. You are assigning your dynamic sql directly to a variable. Even if you got past all the current syntax errors you would have all sorts of other issues.

    consider the following:

    declare @assets money = 0.00;

    SET @assets= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'

    +' where'+' Account.AccountType'+ '=' +1+ 'and ' +'RecordStatus'+ '='+ '1';

    Here you have declared a money datatype and are attempting to assign it a string value. Of course the string assignment won't work anyway because you have made it far more difficult on yourself than you need to and have tried to add a string and an integer and assign it to a money datatype.

    What you have there is something like this:

    declare @MyMoney money

    set @MyMoney = 'asdf' + 10

    To use dynamic sql you have to build your sql string and then execute it.

    Something more like this.

    SET @SQL = N'select SUM(isnull(Balance, 0)) from #AcctBal INNER JOIN ' + @dbname + '.dbo.Account ON #AcctBal.AccountID = Account.AccountID where Account.AccountType = 1 and RecordStatus = 1';

    select @SQL

    I put together a VERY simplified version of your code that should illustrate how to put this together. I removed most of the stuff in there because it is all way off target.

    DECLARE @dbname VARCHAR(50);

    DECLARE @SQL NVARCHAR(MAX) = '';

    declare @Assets money

    DECLARE dbloop CURSOR FOR

    SELECT NAME

    FROM sys.databases

    WHERE NAME NOT IN ('master','model','msdb','tempdb')

    AND State_desc = 'ONLINE'

    OPEN dbloop

    FETCH NEXT FROM dbloop INTO @dbname

    WHILE (@@FETCH_STATUS = 0)

    BEGIN

    SET @SQL = N'select @_Assets = SUM(isnull(Balance, 0)) from #AcctBal INNER JOIN ' + @dbname + '.dbo.Account ON #AcctBal.AccountID = Account.AccountID where Account.AccountType = 1 and RecordStatus = 1';

    exec sp_executesql @SQL, N'@_Assets money OUTPUT', @_Assets = @Assets OUTPUT

    select @Assets as Assets, @dbname as DBName

    FETCH NEXT FROM dbloop INTO @dbname

    END

    CLOSE dbloop

    DEALLOCATE dbloop

    Now as I said before you really need to not use MSDB to hold your table and all the other things you are using here. You need to step back and rethink what you are trying to do here. Read up on dynamic sql.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

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

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