SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


dynamic query..Help needed pls


dynamic query..Help needed pls

Author
Message
prathibha_aviator
prathibha_aviator
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 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:-):-)--------------------------------------------------------------------------------
Dave62
Dave62
Hall of Fame
Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)Hall of Fame (3.6K reputation)

Group: General Forum Members
Points: 3585 Visits: 2836
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!
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65238 Visits: 17979
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.

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)
prathibha_aviator
prathibha_aviator
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 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:-):-)--------------------------------------------------------------------------------
prathibha_aviator
prathibha_aviator
SSC Veteran
SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)SSC Veteran (240 reputation)

Group: General Forum Members
Points: 240 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:-):-)--------------------------------------------------------------------------------
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65238 Visits: 17979
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 Modens 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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65238 Visits: 17979
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.

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)
sqlbi.vvamsi
sqlbi.vvamsi
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 412
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)
Sean Lange
Sean Lange
SSC Guru
SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)SSC Guru (65K reputation)

Group: General Forum Members
Points: 65238 Visits: 17979
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.

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)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search