how to call store procedure - SQL Server Forums

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)

how to call store procedure

Author

Topic

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-12 : 05:17:43

Hi, how to call the shrink_file proc from shrink_file_caller proc as below??



CREATE PROCEDURE [dbo].[Shrink_File] 
@DBName VARCHAR(100),
@err INT OUTPUT
AS
...
GO

CREATE PROCEDURE [dbo].[Shrink_File_Caller] 
@DBName VARCHAR(100),
@err INT OUTPUT
AS
SET NOCOUNT ON;	
DECLARE @nsql NVARCHAR(4000);

SET @nsql = '' + @DBName + '.dbo.Shrink_File ''' + @DBName + ''', @err OUTPUT' ;
EXEC sp_executesql @nsql,N'@err INT OUTPUT', @err = @err OUTPUT;

GO

declare @err int;

EXEC Shrink_File_CALLER 'DATABASE1', @err

select @err as err;

I get the error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'DATABASE1'.

(1 row(s) affected)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 05:21:42

My head spins...

You call the Shrink_File_CALLER sp with a specified database name, right?
In that sp, you call dbo.Shrink_File with CURRENT database name, not the one passed as parameter?

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-12 : 05:27:52

quote:
Originally posted by Peso

My head spins...

You call the Shrink_File_CALLER sp with a specified database name, right?
In that sp, you call dbo.Shrink_File with CURRENT database name, not the one passed as parameter?

E 12°55'05.25"
N 56°04'39.16"

assume that @DBName = 'DATABASE1'

So when I execute shrink_file_caller (in database1),

EXEC Shrink_File_CALLER 'DATABASE1', @err

it will call shrink_file and pass in the @DBName and pass out the @err code

I just want to get the @err value...sorry if it is confusing

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2008-03-12 : 05:37:18

Try placing

SET @nsql = 'EXEC ' + @DBName + '.dbo.Shrink_File ''' + @DBName + ''', @err OUTPUT' ;

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 05:38:23

Try

SET @nsql = QUOTENAME(@DBName) + '.dbo.Shrink_File ' + QUOTENAME(@DBName, ''''), @err OUTPUT'

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-12 : 06:19:14

quote:
Originally posted by Peso

Try

SET @nsql = QUOTENAME(@DBName) + '.dbo.Shrink_File ' + QUOTENAME(@DBName, ''''), @err OUTPUT'

E 12°55'05.25"
N 56°04'39.16"

seems something wrong with the code...


SET @nsql = QUOTENAME(@DBName) + '.dbo.Shrink_File ' + QUOTENAME(@DBName, ''''), @err OUTPUT'

the single quote in red shouldn't be there right??

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2008-03-12 : 06:22:25

There was a typo. He meant this:

SET @nsql = 'EXEC ' + QUOTENAME(@DBName) + '.dbo.Shrink_File ' + QUOTENAME(@DBName, '''') + ', @err OUTPUT'

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-03-12 : 06:23:36

Thanks Harsh.

E 12°55'05.25"
N 56°04'39.16"

juicyapple
Posting Yak Master

176 Posts

Posted - 2008-03-13 : 20:43:04

Thanks all. It is working now.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-03-14 : 06:22:18

Also make sure you read this fully
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources