stored proc

Question

Post reply

stored proc

Mike Levan

SSChampion

Points: 14201
More actions
January 3, 2008 at 11:43 am

#185146

ALTER PROCEDURE importemp
@dbname varchar(30)
AS
begin tran
truncate tblemp
exec(
'use ' + @DBName + ';
DECLARE @div char(20)
SET @div=(select Division from Employees.dbo.tblsector where div=@dbname)
INSERT INTO Employess.dbo.tblEmp
(
EmpID,
EmpNo,
EmpPos,
Division
)
SELECT
EmpID,
EmpNo,
EmpPos,
@div
FROM empold'
)
commit tran
when I run
EXEC 'EmployeesOld' am not getting the value for @div, its returning null.
I think when i run proc with param , its value is not passed to @dbname in the SET

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 1

First things, first... call to the proc should be...

EXEC importemp 'EmployeesOld'

... not what you posted.

Second, you have a coding error... correction is highlighted in red...

ALTER PROCEDURE importemp

@dbname varchar(30)

AS

begin tran

truncate tblemp

exec(

'use ' + @DBName + ';

DECLARE @div char(20)

SET @div=(select Division from Employees.dbo.tblsector where div=' +QUOTENAME(@dbname,'''') +')

INSERT INTO Employess.dbo.tblEmp

(

EmpID,

EmpNo,

EmpPos,

Division

)

SELECT

EmpID,

EmpNo,

EmpPos,

@div

FROM empold'

)

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Mike Levan SSChampion Points: 14201 More actions · Answer 2

thanks jeff..

in the same situation how can i pass parameters to a store proc executed inside another proc.

inner proc will be like

ALTER PROCEDURE spgetemp

@empname varchar(30),

@jdate datetime

AS

SET NOCOUNT ON

insert into #t exec spcheckemp '@empname','@jdate'

select * from #t

.....

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

Since there is no need for dynamic SQL in this case, just remove the quotes from the variable names... like this...

ALTER PROCEDURE spgetemp

@empname varchar(30),

@jdate datetime

AS

SET NOCOUNT ON

insert into #t exec spcheckemp @empname, @jdate

select * from #t

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

In that case, since you're not building up a string, you don't want the quotes. In the first case, you were building up a string to be executed, so the value of the parameter had to be concatenated into the string.

With the the quotes, what's passed to the stored proc is the strings '@empname' and '@jdate', not tha values of the variables.

ALTER PROCEDURE spgetemp

@empname varchar(30),

@jdate datetime

AS

SET NOCOUNT ON

insert into #t exec spcheckemp @empname,@jdate

select * from #t

Does that make sense?

That said, why are you bothering with the temp table #t at all when you just select from it straight. Any reason why you don't just call the procedure?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Mike Levan SSChampion Points: 14201 More actions · Answer 5

as you passing string and values... I have a doubt

in this proc

ALTER PROCEDURE spgetemp

@empname varchar(30),

@jdate datetime

AS

SET NOCOUNT ON

insert into #t exec spcheckemp @empname,@jdate

set @cmd='

select * from #t where joindate>='+ ''''+ @jDate + ''''+'

'

this is just an example. in the above dynamic sql scenario does the where clause work and pull up the records accroding to the date?

when i did that it just gave out all the records instead of the date specified?

Mike Levan SSChampion Points: 14201 More actions · Answer 6

sorry guys to add another doubt here

@dbname varchar(30),

@jDate DateTime

AS

Begin

exec(

'use ' + @dbname + ';

sql statements (14k lcharcters includes 30+ union operators)

'

end

when i try executing above proc i get a dead lock error, i just want to know the reason.

is it because am using 14k characters or because i am using 30+ union operators.

and how can i avoid that error.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

14k characters? What on earth are you doing?

Still, Exec will handle many more than 14K characters... so that's not the problem. Can't tell what's causing the problem 'cause can't see the code...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Turn traceflag 1204 on (DBCC TRACEON (1204)) then run your proc. You'll get a deadlock graph written to the error log that will tell you the two processes involved in the deadlock and what resources they were deadlocked over.

I am getting curious though. Why do you need to dynamically switch databases and run massive union statements? Could you explain the reasons behind this? Maybe there's a better solution.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 9

Heh... ten to one says monthly, weekly, or even daily transaction databases... folks do that a lot especially in the telephone business (Call detail records saved in monthly databases based on billing date).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 10

Or databases per customer. I've seen that before. I haven't yet had the 'pleasure' of working on monthly databases.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass