See what variables I am passing to a procedure

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

General SQL Server Forums

New to SQL Server Programming

See what variables I am passing to a procedure

Author

Topic

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 15:19:22

Hi,

I am encountering a problem when I execute a procedure, which input parameters are passed from a table. It will be very helpful to me if I can find a way to see what are the eaxct variables values that I am passing from the table - any ideas how I can do that? I think the problem might be formatting..

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 15:44:12

I think the problem comes from the formatting of the DATE in the table that I get the values from. There, I insert the dates manually in this format 'YYYY-MM-DD'.

This is wrong i was told, so what is the right format to insert DATETIME value into sql server 2005 table.

Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-09 : 15:53:12

The DATETIEM datatype is stored as two integers and doesn't have any formatting. You can take a datetime value and manpulate the display of the that value (format it).

Do you have a sample of what is not working and how (does it fail or does it produce the wrong result or ..)?

Maybe this link will help:
http://www.sql-server-performance.com/articles/dev/datetime_datatype_p1.aspx

FYI, the ISO8601 date style is yyyy-mm-ddThh:mi:ss.mmm (no spaces), if that helps.

To see what you are passing depends on the data type. But, some options:
1. Print the value.
2. Use the Raiserror function with a severity of 10 or lower.
3. Set up debugging and run a trace.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 16:26:41

ok so basically these are the steps and values that I am working with. I dont receive any error but just 0 rows are affected when executing the procedure.

1) Create a table "Values" (Date DATETIME) and insert values ('2010-01-01')
2) Have a procedure X with input parameters (@Date SMALLDATETIME). Procedure results after a calculation that adds 1 day or the input date are inserted into a new table Results
3) I want to execute this procedure by passing the value from Table Values into the Procedure Input Parameter. I do the following

Declare @Date DATETIME

exec procedure X @Date
Set @Date = Date from Values

4) Result is that 0 rows are affected and no values are inserted into the table Results.

When I replace the actual @DATE input with this value '2010-01-02', the procedure executes successfully and I get the output results! Where could the problem be? thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2010-12-09 : 16:40:54

Declare @Date DATETIME
SELECT @Date = Date from Values
exec procedure X @Date

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 16:47:20

I tried your version too Rob but it is still not working...

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 17:16:50

To add a little more information - I used the Print function and the Date value that I am passing from the exec Procedure Input to the actual Procedure looks like that May 1 2010 12:00AM

Is this formatted the right way ?

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 17:49:46

basically the question comes down to How do I pass DATETIME values from a table to a procedure? I follow the same steps that I listed above for float values and it works! so how come with DATETIME it is different, I dont understand?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-09 : 17:53:06

There shouldn't be any issue. Unless you are converting the dataype to something else (like a string). I ran a quick test on my machie and it seems to work just fine. Does this work for you?

-- Setup
CREATE TABLE dbo.Value (MyDate DATETIME)
CREATE TABLE dbo.Result (MyDate DATETIME)

INSERT dbo.Value (MyDate) VALUES ('2010-03-01')
GO

CREATE PROCEDURE dbo.X (@DateVal SMALLDATETIME)
AS
BEGIN
    INSERT dbo.Result (MyDate) VALUES(DATEADD(DAY, 1, @DateVal))
END
GO

-- Trial
TRUNCATE TABLE dbo.Result

DECLARE @Date AS DATETIME

SET @Date = (SELECT MyDate FROM dbo.Value)

EXEC dbo.X @Date

SELECT @Date, MyDate FROM dbo.Result

-- Teardown
DROP PROCEDURE dbo.x
DROP TABLE dbo.Result
DROP TABLE dbo.Value

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 18:00:43

it worked! you do two things that I have not included in my version of the procedure:

1) Truncate Table

2) Declare @date AS DATETIME (adding 'as')

Can you please explain what purpose these serve? Thanks!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2010-12-09 : 18:37:17

The Truncate I had in there so I could re-run the trial and show that I value was inserted. Bascailly, always start with an emtpy table. The "AS" in the declare statement was actually a cut-n-paste error. It appears to work, but it is not needed and should be removed.

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 18:51:16

I changed a litte bit your script to add a looping statement and it is not executing properly again!

Can you tell what is the problem?

CREATE PROCEDURE dbo.X (@DateVal SMALLDATETIME)
AS

Declare @AllDate DATETIME
Set @AllDate = @DateVal
while @AllDate <= '2010-04-01'

BEGIN
INSERT dbo.Result (MyDate) VALUES( @AllDate)

set @AllDate = DATEADD(DD, 1, @AllDate)
END
GO

EXEC dbo.X '2010-03-01'

(I eliminated all tables because there is a problem with the procedure itself. Once I fix that, then will pass the value from the table. thanks!

VeselaApo
Posting Yak Master

114 Posts

Posted - 2010-12-09 : 18:53:32

actually ignore my previous message - it is working when I tried again. So let me add values from a table now...

X002548
Not Just a Number

15586 Posts

Posted - 2010-12-09 : 21:16:52

<smacks forehead>
oye
</smacks forehead>

I need to know what alcohol I am drinking

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources