2 Replies - 230 Views - Last Post: 26 April 2019 - 06:07 AM

#1 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,210
  • Joined: 12-January 10

scalar variable not passing to a exec stored procedure

Posted 26 April 2019 - 05:39 AM

hi,

i am trying to pass a scalar variable to a stored procedure however no data is returned. If i place a number in its spot the data returns:

code:
ALTER PROCEDURE [dbo].[aaa_spTest]
--exec dbo.aaa_spTest 'HP',1
	-- Add the parameters for the stored procedure here
	(
		@_case varchar(1),
		@_plantID int
	)

AS
BEGIN
Declare @PID int = @_plantID
if @_case = 'S'
		Begin
			exec dbo.aaa_spShiftShiftDDLGet
		End	   
if @_case = 'P'
		Begin
			exec dbo.aaa_spShiftPlantDDLGet  
		End
if @_case = 'HP'
		Begin
			exec dbo.aaa_spHourlyProductDDLGet  @PlantID = @PID <--doesnt return anything   

		End
if @_case ='HR'
		Begin
			exec dbo.aaa_spHourlyReasonDDLGet    <--this one also doesnt return anything
		End
		Else Return

	 --SET NOCOUNT ON added to prevent extra result sets from
	 --interfering with SELECT statements.
	SET NOCOUNT ON;

 
  
END


if i do it like the following it returns data
exec dbo.aaa_spHourlyProductDDLGet  1


if i run this by itself data is returned -- i think this one might not work because the previous one isnt working
exec dbo.aaa_spHourlyReasonDDLGet


Is This A Good Question/Topic? 0
  • +

Replies To: scalar variable not passing to a exec stored procedure

#2 DarenR   User is offline

  • D.I.C Lover

Reputation: 634
  • View blog
  • Posts: 4,210
  • Joined: 12-January 10

Re: scalar variable not passing to a exec stored procedure

Posted 26 April 2019 - 05:55 AM

found it-- dumb ass me declared varchar(1) instead of 2
Was This Post Helpful? 0
  • +
  • -

#3 baavgai   User is offline

  • Dreaming Coder
  • member icon


Reputation: 7495
  • View blog
  • Posts: 15,531
  • Joined: 16-October 07

Re: scalar variable not passing to a exec stored procedure

Posted 26 April 2019 - 06:07 AM

You, um, defined your @_case as varchar(1). How can that hold 'HP'?

I rather hate the underscores and this, frankly, seems silly: Declare @PID int = @_plantID.

Curiously, T-SQL is one of the ONLY places where I find gotos acceptable. This works, as a quick example:
create procedure UpFoo
as
select GETDATE()
go

create procedure UpBar(@n int)
as
select @n * 2
go

create procedure UpBaz(@choice char(1), @n int = 1)
as
begin
  if @choice = 'B' goto bar
  if @choice = 'F' goto foo
  goto fail

bar:
  exec UpBar @n
  goto done

foo:
  exec UpFoo
  goto done

fail:
  select @choice + ': Oops, choose B or F'

done:
end
go


exec UpFoo
exec UpBar 2

exec UpBaz 'X'
exec UpBaz 'B', 4
exec UpBaz 'F'
go


Was This Post Helpful? 1
  • +
  • -

Page 1 of 1