Why not print - SQL Server Forums

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)

Why not print

Author

Topic

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-16 : 18:02:39

Hello,
I test a simple query, the result is correct but why no print.

Thanks

declare @qry1 nvarchar(455)
declare @d    datetime
set @qry1 = N'select mydate from mytable'
execute sp_executesql @qry1,N'@d datetime output',@d output
print dateadd(day,1,@d)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-16 : 18:18:08

The value is printed. However not on your printer but on the Messages tab in SSMS.

E 12°55'05.63"
N 56°04'39.26"

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-16 : 18:30:33

And if you are running it in SSMS only and not seeing what you are printing, then may be you have 'results to grid' option enabled. You can see it by switching to 'results to text'(Ctrl+t)

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 09:39:00

The result of
execute sp_executesql @qry1,N'@d datetime output',@d output
can be seen. But why the result of
print dateadd(day,1,@d)
can not been seen.
I saw 1 row affected in message tab.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 09:46:31

Did you change the result option setting to 'results to text'??

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 10:09:49

I did it.Then message tab disappeared.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-17 : 10:25:55

The most likely cause is that @d is null.

Try adding this at the end of your code to see if it is null:

select [@d] = @d

CODO ERGO SUM

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 10:32:40

Yes. It's null.
Why?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2009-03-17 : 10:37:46

quote:
Originally posted by lovehui

Yes. It's null.
Why?

That is up to you to determine.

CODO ERGO SUM

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 10:41:10

Honestly, I don't know. It looks correctly of the query.
I just want to assign it to @d.

Still need advice.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 10:44:15

check your query. Find out why its returning null.

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 10:48:17

select mydate from mytable

It is a correct query and a value returned.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 11:29:23

try this,

declare @qry1 nvarchar(4000)
declare @d datetime

set @qry1='select @d=mydate from mytable'
execute sp_executesql @qry1,N'@d datetime output',@d output
select dateadd(day,1,@d)

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 11:31:50

Incorrect syntax

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 11:33:35

post the full message.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 11:34:30

You sure you took the edited query?? @ 11:29:44??

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 11:51:52

declare @qry1 nvarchar(455)
declare @d datetime

set @qry1 = N'select @d = select BUS_DT from STORE_STATUS'

execute sp_executesql @qry1 ,N'@d datetime output',@d output
select @d
print dateadd(day,1,@d)

Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'select'.

-----------------------
NULL

(1 row(s) affected)

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 11:52:53

[code]declare @qry1 nvarchar(455)
declare @d datetime

set @qry1 = N'select @d = ~~select~~ BUS_DT from STORE_STATUS'

execute sp_executesql @qry1 ,N'@d datetime output',@d output
select @d
print dateadd(day,1,@d)[/code]

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 12:00:20

Great!

Appreciate.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-17 : 12:01:49

nice :)

lovehui
Yak Posting Veteran

60 Posts

Posted - 2009-03-17 : 14:50:44

When I place it in a stored procedure I got an error.
Incorrect syntax near '-'
But I haven't found '-' in line
execute sp_executesql @qry1 ,N'@d datetime output',@d output

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources