Alternatives to write an 'Execute' instruction

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 2000 Forums

Transact-SQL (2000)

Alternatives to write an 'Execute' instruction

Author

Topic

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-02 : 06:36:12

Hi,

I want to test in Query Analyser a SP that does a very long query and returns a lot of parameters (@). Is this the only way to write an ‘Execute’ instruction?:


Declare @Ref varchar(50)
...
EXEC offer_detail
@Offer_num = '512', @Reference = @Ref output,...

Select @Ref
...

I find it’ s very long to declare, set and select all the variables.. Isn’ t there a shorter way?

Thank you

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 06:56:31

That's pretty much it - unless all the same data is available in a table row, or somesuch.

You could create offer_detail with suitable default values and then leave off the EXEC statement any parameters for which the default value is appropriate.

Why are there so many parameters?

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-09-02 : 08:48:23

In Query Analyzer, right click on the stored proc you wish to test and there are several options:

1) Open -- prompts your for parameters
2) Script Object to (New Window/Clipboard/File) As Execute -- generates some T-SQL for you to use

Remember: When in doubt about what options you have on an object when working with a windows application, always Right-Click !

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 09:55:44

Not quite the same thing, but highlighting the name and pressing Alt-F1 will give you info about the SProcs's parameters

Krsiten

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-02 : 10:44:50

Thanks, I see there are many tools

. The original problem is I have a SP that does a query and returns parameters to the app to show an 'offer details' page, but I receive an error in the app (Cast from type 'DBNull' to type 'String' is not valid), I mean that for all the parameters I receive null values!, when they have values in its columns..

Here a little sample of the SP:


Use market5
GO
ALTER PROCEDURE offer_detail
@Offer_num bigint, @Reference varchar(50) output, @City varchar(50) output,...
As


SET NOCOUNT ON 

Select @Reference = Reference, @City = city_user.City_name,...

From Offers As offe 

JOIN Users As use
On offe.User_num = use.User_id
JOIN cities As city_user
On use.City_num = city_user.City_id
JOIN States As state_user
On state_user.State_Id = city_user.State_num  
...

Where Offer_id = @Offer_num

GO

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-02 : 11:37:45

I see that there is a 'join' that causes the problem:


JOIN Spain_cities As city_Prod
On offe.City_origin_num = city_Prod.City_id

If I drop this 'join' all works fine, if I include it then that error happens.
Why this error may happen? If the column 'city_Prod.City_name' (which I use in select statement) is null in some records can be a problem? I don' t think so.. (The null exception only would happen in that field)

Kristen
Test

22859 Posts

Posted - 2005-09-02 : 11:48:31

If the app will not accept a NULL in the column you could do

SELECT ... COALESCE(city_Prod.City_name, '') AS City_name, ...

so that an empty string is returned instead of a NULL

Kristen

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-02 : 12:07:47

or change that JOIN to a LEFT JOIN?

Plus, you could handle that NULL in your presentation layer.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-02 : 12:13:49

No, the problem is that if I include that join (tested in Query Analyzer) the rest of the parameters are all null, when most of them aren' t null. And vice versa, if I erase that join the rest of the parameters shows its values (not null)

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-02 : 13:06:05

I will try to solve it later. Anyway thank you!

Have a nice weekend

cesark
Posting Yak Master

215 Posts

Posted - 2005-09-05 : 09:55:26

DonAtWork! You said the solution

'change that JOIN to a LEFT JOIN' and I didn' t realize.. Indeed, I didn' t know its existence. But I don' t understand very well the difference between 'left join' and 'right join', and the difference between 'join' and 'inner join'.

Thank you

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-09-05 : 11:00:12

"But I don' t understand very well the difference between 'left join' and 'right join', and the difference between 'join' and 'inner join'"....read up on it then in Books-On-Line (BOL)...(JOIN and INNER JOIN are the same thing...while LEFT/RIGHT are NOT)

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2005-09-06 : 10:46:00

ooo i got one right

yay me. And Andrew points you to the best place for the difference in JOINS.

Help us help YOU!
Read this blog entry for more details: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx

*need more coffee*
SELECT * FROM Users WHERE CLUE > 0
(0 row(s) affected)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources