Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

dynamic query..Help needed pls Expand / Collapse
Author
Message
Posted Tuesday, January 22, 2013 10:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, Visits: 271
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--------------------------------------------------------------------------------
Post #1410163
Posted Tuesday, January 22, 2013 11:03 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 29, 2014 10:41 AM
Points: 2,046, Visits: 2,045
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!
Post #1410172
Posted Tuesday, January 22, 2013 11:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1410175
Posted Tuesday, January 22, 2013 11:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, Visits: 271
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--------------------------------------------------------------------------------
Post #1410177
Posted Tuesday, January 22, 2013 11:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 13, 2014 1:48 PM
Points: 104, Visits: 271
[b]
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--------------------------------------------------------------------------------
Post #1410182
Posted Tuesday, January 22, 2013 12:09 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
prathibha_aviator (1/22/2013)
[b]
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1410209
Posted Tuesday, January 22, 2013 12:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1410213
Posted Tuesday, January 22, 2013 12:29 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, August 28, 2014 9:05 AM
Points: 54, Visits: 359
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)
Post #1410221
Posted Tuesday, January 22, 2013 12:57 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 2:32 PM
Points: 13,302, Visits: 12,168
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1410235
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse