execute certain part of sp based on input param

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

Transact-SQL (2008)

execute certain part of sp based on input param

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-14 : 18:01:49

I have a stored procedure that contains three different select statements.
Can I pass in a variable that determines which select statement to run for my result set?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-14 : 18:08:04

Yes.

create proc zxc
(@v1 varchar(5))
as

if @v1 = 'asdf'
select 1...
else if @v1 = 'qwert'
select 2...
else if @v1 = 'zxd'
select 3...
else
raiserror...

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-14 : 18:25:04

Thank you.
That works fine when I execute the stored procedure regulary, owever, when I wrap it into a BCP command only the first SELECT is executed correctly. the second and third just return 1 row.


Exec Master..xp_Cmdshell 'bcp "EXEC DB_RPT..sp_create_File_all ''a''" queryout "C:\temp\export_1.txt" -T -c'

If I pass in 'b' or 'c' into the sp I don't get a complete result set.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-14 : 18:49:04

thats not true. Can you post used code? I think thats because of problem with way you've written the code

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-19 : 13:34:36

OK. That seems possible.
Here's the code for the stored procedure that I am using:

CREATE PROCEDURE [dbo].sp_create_File_all (@Type char(1))

AS
BEGIN
	SET NOCOUNT ON;

    -- Insert statements for procedure here
IF @Type='a'	
SELECT 
  a.ArticleNumber
, a.ArticleName
, a.UnitOfMeasure
, b.ArticlePrice
FROM DimArticle a
INNER JOIN DimPrices b ON a.ArticleNumber = b.ArticleNumber
WHERE a.ArticleStatus = 1 


else IF @Type='b'

SELECT 
  a.ArticleNumber
, a.ArticleName
, a.UnitOfMeasure
, b.ArticlePrice
, a.PublicationCode
FROM DimArticle a
INNER JOIN DimPrices b ON a.ArticleNumber = b.ArticleNumber
WHERE a.ArticleStatus = 1 

else IF @Type='c'

SELECT 
  a.ArticleNumber
, a.ArticleName
, a.UnitOfMeasure
, b.ArticlePrice
, a.PublicationCode
, a.PackageWeight
FROM DimArticle a
INNER JOIN DimPrices b ON a.ArticleNumber = b.ArticleNumber
WHERE a.ArticleStatus = 1 

END

And this is the BCP command that I am executing:


Exec Master..xp_Cmdshell 'bcp "EXEC DB_RPT..sp_create_File_all ''a''" queryout "C:\temp\export_1.txt" -T -c'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-09-19 : 14:08:16

you've same logic for all cases? then whats the purpose of if conditions?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-19 : 14:59:46

It's not quite the same. Based on the input parameter the SELECT returns more columns:
- PublicationCode
- PackageWeight

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-19 : 15:37:21

Can you use a view instead? I have a feeling bcp just isn't able to support this. This can easily be changed to a view, which bcp can handle.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2012-09-19 : 17:14:01

Thank you for that suggestions. I'm not very familiar with the works of a view. Would I just SELECt all clumns for the view and then call a SELECT with BCP rather than a stored procedure?

quote:
Originally posted by tkizer

Can you use a view instead? I have a feeling bcp just isn't able to support this. This can easily be changed to a view, which bcp can handle.

Tara Kizer

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2012-09-19 : 18:05:23

Yes you would select all columns in the view, and the view would include the where clause you've got. Then use you would just use a select in the bcp command, the select would just pull the columns you want from the view.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources