Passing variables into a SProc

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

General SQL Server Forums

New to SQL Server Programming

Passing variables into a SProc

Author

Topic

Shrews14
Starting Member

37 Posts

Posted - 2007-01-17 : 06:35:34

If anybody could help me with this i would love to hear from you, at the moment this is driving me crazy?

now i have a stored procedure in SQL server 2000, which i need to pass some variables into (pretty straight forward so far!!) I am copying tables from one database to another and i what to be able to choose the spefic database i'm copying to, this is the basic's of my query!

CREATE PROC sp_Copy_Facility
@FacilityID int,

/* Select and append Data tables */

-SELECT testDB.dbo.dt_test.* INTO
-******.dbo.dt_sample
-FROM testDB.dbo.dt_test
-WHERE testDB.dbo.dt_test.Facility_ID = @FacilityID
-option (keep plan)

i have also tried this:

CREATE PROC sp_Copy_Facility
@FacilityID int,
@DBIN nvarchar(40),
@DBOUT nvarchar(40)
AS

DECLARE @SQLString varchar(1000)
DECLARE @S2 nvarchar(1000)

/* Select and append Data tables */

SET @SQLString = 'SELECT ' + @DBIN +'.dbo.dt_test.* INTO '
SET @SQLString = @SQLString + @DBOUT + '.dbo.dt_test
SET @SQLString = @SQLString + 'FROM ' + @DBIN + ' .dbo.dt_test WHERE ' + @DBIN + '.dbo.dt_test.Facility_ID = @FacilityID option (keep plan)'

please help i need to get this done and i posted this on a number of different web sites with as yet no joy!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 06:47:39

[code]SET @SQLString = 'SELECT ' + QUOTENAME(@DBIN) +'.dbo.dt_test.* INTO '
SET @SQLString = @SQLString + QUOTENAME(@DBOUT) + '.dbo.dt_test '
SET @SQLString = @SQLString + 'FROM ' + QUOTENAME(@DBIN) + '.dbo.dt_test WHERE ' + QUOTENAME(@DBIN) + '.dbo.dt_test.Facility_ID = ' + CAST(@FacilityID AS VARCHAR) + ' OPTION (KEEP PLAN)'

EXEC (@SQLString)[/code]

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 06:48:58

You have extra spaces cluttered all over the place in the SQLString!
Also you didn't check for spaces or other special characters in the @DBIN and @DBOUT parameters.

Peter Larsson
Helsingborg, Sweden

Shrews14
Starting Member

37 Posts

Posted - 2007-01-17 : 07:02:58

thanks for the reply, i'm a very new to programming and databases and have taken it on at a fast pace, so i'm sure that i have a number of bad habits and silly mistakes in my syntax.

Thanks again!!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-17 : 07:11:03

A VERY good practice is to replace EXEC (@SQLString) with PRINT @SQLString and copy the result to Query Analyzer and run the pasted code.
Then you will see all errors with spaces and so on.

Peter Larsson
Helsingborg, Sweden

Shrews14
Starting Member

37 Posts

Posted - 2007-01-17 : 07:19:43

Just run this and i see the Analyzer returns the whole SQL string and, it becomes alot easier to spot errors.

thanks i will be using this forum more often i think, it's and excellent resource!

Shrews14
Starting Member

37 Posts

Posted - 2007-01-17 : 07:34:13

could i use the same method with this SProc,

REATE PROC sp_Create_Database
@SQLPath nvarchar(40),
@DatabaseName nvarchar(40)

AS

CREATE DATABASE @DatabaseName

ON
(NAME = @DatabaseName , FILENAME = @SQLPath + @DatabaseName + '.mdf', SIZE=20, MAXSIZE=50,FILEGROWTH = 5)
LOG ON
(NAME = @DatabaseName + 'log', FILENAME = @SQLPath + @DatabaseName + 'log.ldf', SIZE=10MB, MAXSIZE=25MB, FILEGROWTH = 5MB)
GO

and then possibly call on this SProc in the copy_facility SProc in the other posts.

Shrews14
Starting Member

37 Posts

Posted - 2007-01-17 : 10:15:35

Hi all,

i've applied the the above and come up with this:

Create PROC sp_Create_Database
@SQLPath nvarchar(40),
@DatabaseName nvarchar(40)

AS

CREATE DATABASE copyfac

ON
(NAME = QUOTENAME(@DatabaseName), FILENAME = QUOTENAME(@SQLPath) + QUOTENAME(@DatabaseName) + '.mdf', SIZE=20, MAXSIZE=50,FILEGROWTH = 5)
LOG ON
(NAME = QUOTENAME(@DatabaseName) + 'log', FILENAME = QUOTENAME(@SQLPath) + QUOTENAME(@DatabaseName) + 'log.ldf', SIZE=10MB, MAXSIZE=25MB, FILEGROWTH = 5MB)
GO

This creates a syntax error!

Why?

I'm very new to programming

agossage
Starting Member

9 Posts

Posted - 2007-01-17 : 17:52:55

You need to execute the dynamic SQL using EXEC or sp_executesql.

Use this for the body of the procedure...
exec ('CREATE DATABASE test ON (NAME = ''' + @DatabaseName + ''', FILENAME = ''' + @SQLPath + @DatabaseName + '.mdf'', SIZE=20, MAXSIZE=50,FILEGROWTH = 5) LOG ON (NAME = ''' + @DatabaseName + '_log'', FILENAME = ''' + @SQLPath + ' + ' + @DatabaseName + 'log.ldf'', SIZE=10MB, MAXSIZE=25MB, FILEGROWTH = 5MB)')

Adam Gossage
Lake Wylie, SC, USA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources