where I am going wrong in this code ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

where I am going wrong in this code ?

Author

Topic

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-27 : 21:39:24

Please let me know where my code is wrong.

Stored Procedure gets created but does not work when try to execute

There is table called Production.ProductReview
The columns are ProductReviewId, ProductId , Ratings and Comments

There are only 4 rows in the table.

The task is to write a stored procedure with input parameter as Product Review Id and
Output parameter as ProductId , Ratings and Comments

My code is as follows

Use AdventureWorks
Go
Create Procedure dbo.ProductReview1
@ReviewId int
,@ProductId int output
,@Rating int output
,@comments nvarchar output

As
Select @ReviewId = Pr.ProductReviewID from Production.ProductReview as Pr
Select @ProductId =Pr.ProductID from Production.ProductReview as Pr
Select @Rating = Pr.Rating from Production.ProductReview as Pr
Select @Comments =Pr.Comments from Production.ProductReview as Pr

Declare @ProductId int ,@Rating int ,@comments nvarchar
Execute ProductReview1 2 @ProductId, @Rating,@Comment
Print @ProductId, @Rating,@Comment

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-10-27 : 21:52:22

homework question?

you need to combine all of your output assignments into one select statement, and get rid of that stuff at the bottom

select @outParam1 = xxx, @outParam2 = yyy
from yourTable
where zzz = @inputParam

etc...

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2007-10-27 : 22:59:31

Select @ProductId = productid,
@Rating = rating,
@Comments = @comments
from
Production.ProductReview
where ProductReviewID = @ReviewId

ganatra.neha
Starting Member

30 Posts

Posted - 2007-10-28 : 01:11:45

hank you so much

Only one issue.

When i type following code only 1st letter is dispalyed in comments column

Declare @ProductId int , @Rating int, @comments varchar(3850)
Execute ProductReview1 1, @ProductId output , @Rating output, @comments output
Print @ProductId
Print @Rating
Print @comments

Kristen
Test

22859 Posts

Posted - 2007-10-28 : 02:38:15

In your Sproc you have defined @comments with no size, the default is one character.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-10-29 : 03:16:53

quote:
Originally posted by Kristen

In your Sproc you have defined @comments with no size, the default is one character.

Kristen

As usual I expect it to return error saying "Column length is not specified" than debugging where it goes wrong. I still dont unserstand the advantage of implicitely converting to size 1

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:07:59

"I still dont unserstand the advantage of implicitely converting to size 1"

Design / bug issue way back that is allowed to remain. 100% daft. All it needs is some backward-compatibility flags in the DB that are, for new databases, off by default.

Even old timers like you and I can fall into this huge hole and waste time tracking down the cause. It happens with the newbies here, and I'm never looking in the right place to spot the cause because I just don't code without a size on char/varchar/etc.

Don't you have an MVP hotline you can call, and complain, Madhi?

Kristen

Kristen
Test

22859 Posts

Posted - 2007-10-29 : 05:12:48

The OP has deleted one of their posts, it does annoy when people do that

In fact checking their account the OPs post count is 9 but I can only see 6 posts. Might be 2 posts made in error, but the one deleted wasn't (from memory it was thanking me for helping with the Size parameter, and saying that 1000 or 4000 or somesuch has been used and the result was successful)

Unless Snitz has taken a nose-dive

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources