Replacing SQL script with variables

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Replacing SQL script with variables

Author

Topic

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 10:57:58

Hi..

i tested my sql script for an individual table and i got expected result.

this is my script

SELECT count(*) as INSERT_COUNT FROM RDB_PAS_APPOINTMENTS WHERE INSERT_DATE >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),0)
AND INSERT_DATE < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

now i want this script to replace with variables like below script
,becaz i am not getting expected output with the below script.

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
'Select ''' + @TableName + '''as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
WHERE convert(varchar,INSERT_DATE,101) >= ' + convert(varchar,@TODAY,101) + ''

could any one help me in this query pls?

thanx

Satya

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 11:03:01

[code]
SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''[/code]

Harsh Athalye
http://www.letsgeek.net/

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 11:11:47

thanx..harsh,

its running now,it will take 15 min complete the process..i hope this time it will work without any errors..

let u know once it finish..

thanx again.

Satya

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 11:36:42

HELLO,

WHEN i used this script my query running without any error nut in my output table i am getting all rows with 0 for NO_OF_INSERTS columnis there any changeg to with this script?

the same i am applying for UPDATE_COUNT as well but even that too getting all the rows with 0 value.

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''

thanx

Satya

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 11:57:03

I would say print the content of variable first to check whether the dynamic query you are building is correct or not. Add print statement to print variable value and verify it.

Harsh Athalye
http://www.letsgeek.net/

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-29 : 12:07:29

harsh,,

this is my printed message,i copied this for INSERT_COUNT and UPDATE_COUNT pasted in a new query when i executed getting 0. but when i used the ordinary script without variables i am getting expected output.

Insert into RECORD_COUNT (TABLE_NAME, ROW_COUNT) SELECT 'RDB_PAS_EAS_DETAILS_GEC'as TABLE_NAME, COUNT(*)as ROW_COUNT FROM [RDB_PAS_EAS_DETAILS_GEC]

(1 row(s) affected)
got to step 1
insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS) Select 'RDB_PAS_EAS_DETAILS_GEC' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from RDB_PAS_EAS_DETAILS_GEC
WHERE convert(varchar,INSERT_DATE,101) >= '2010-03-29'

(1 row(s) affected)
got to step 3
insert into UPDATE_COUNT (TABLE_NAME, NO_OF_UPDATES) Select 'RDB_PAS_EAS_DETAILS_GEC' as TABLE_NAME,count(convert(varchar,UPDATE_DATE,101)) from RDB_PAS_EAS_DETAILS_GEC
WHERE convert(varchar,UPDATE_DATE,101) >= '2010-03-29'

script for INSERT_COUNT

PRINT 'got to step 1'

SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
WHERE convert(varchar,INSERT_DATE,101) >= ''' + convert(varchar,@TODAY,101) + ''''

is there anything need to include into the script?

thanx

Satya

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-03-29 : 12:11:51

Your way of comparing datetimes is wrong. You are converting INSERT_DATE column to varchar before comparing it with actual date. This is gonna result in ASCII comparison. The proper way of comparison will be

where dateadd(day, datediff(day, 0, INSERT_DATE), 0) >= '2010-03-29'

I think this may be the reason you are getting count as zero.

Harsh Athalye
http://www.letsgeek.net/

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-30 : 04:09:09

hello..harsh, i am sorry for late reply just i have seen ur reply

i tried replacing ur query in the where clause..

errror message

Msg 102, Level 15, State 1, Line 45
Incorrect syntax near '0'.

this message showing error at this line

where dateadd(day,datediff(day, 0, INSERT_DATE)' 0) >= ''' + convert(varchar,@TODAY,101) + '''

do u have any idea where i have done wrong in this where clause..?

quote:
Originally posted by harsh_athalye

Your way of comparing datetimes is wrong. You are converting INSERT_DATE column to varchar before comparing it with actual date. This is gonna result in ASCII comparison. The proper way of comparison will be

where dateadd(day, datediff(day, 0, INSERT_DATE), 0) >= '2010-03-29'

I think this may be the reason you are getting count as zero.

Harsh Athalye
http://www.letsgeek.net/

Satya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-30 : 05:06:44

To troubleshoot it print the value of @SQL1 and post it here

Madhivanan

Failing to plan is Planning to fail

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-30 : 05:13:28

the complete message is

Msg 102, Level 15, State 1, Line 46
Incorrect syntax near '0'.

(518 row(s) affected)

and it pointing to
SELECT @SQL1 = 'insert into INSERT_COUNT (TABLE_NAME, NO_OF_INSERTS)' +
' Select ''' + @TableName + ''' as TABLE_NAME,count(convert(varchar,INSERT_DATE,101)) from ' + @TableName + '
where dateadd(day,datediff(day, 0, INSERT_DATE)' 0) >= ''' + convert(varchar,@TODAY,101) + '''

thanx

Satya

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 05:59:07

Well the syntax error is caused by a missed comma and the incorrect closing of the sql string.

If you used sp_executeSql and then passed the dates in as paramaters then you wouldn't have to worry about all the converts. Also -- you aren't specifying a size for the VARCHAR's when you convert. That's bad.

Look up sp_executeSql and have a read here. You'll be a better developer because of it.:
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-30 : 05:59:23

The error is at

where dateadd(day,datediff(day, 0, INSERT_DATE)' 0)

You dont need a single quote before 0

Madhivanan

Failing to plan is Planning to fail

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-30 : 06:00:41

quote:
Originally posted by madhivanan

The error is at

where dateadd(day,datediff(day, 0, INSERT_DATE)' 0)

You dont need a single quote before 0

Madhivanan

Failing to plan is Planning to fail

Also you need to put a comma in instead of the quote

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

satya068
Posting Yak Master

233 Posts

Posted - 2010-03-30 : 07:45:59

HELLO..
charlie & madhavan

it worked fine and i got all the inserts and updates with values.

really appreciate ur help..

thanx both of u ...

Satya

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-03-30 : 08:13:13

quote:
Originally posted by satya068

HELLO..
charlie & madhavan

it worked fine and i got all the inserts and updates with values.

really appreciate ur help..

thanx both of u ...

Satya

Good.
It seems that you are having this problem of couting data from the tables as most of your recent topics are related to that

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources