CODEWITHSUNDEEP
sqlsql-server
BruceyBandit
BruceyBandit

Reputation: 4324

how to declare a database

How can I declare a database as when I try the following:

declare @Database1 [$(Database1)]

It states 'Column, parameter, or variable #8: Cannot find data type Database1'.

UPDATE:

So what I am trying to do is create a variable so that it refers to a database, and then I can include that variable in an OUTPUT statement. I'm using visual studio where by I'm using a reference to call the database, but want that reference to be set as a variable:

At the moment I have:

declare @Database1 [$(Database1)]

...

OUTPUT ''+@Database1+'.dbo.Package' 'TableName', 'PackageID', inserted.PackageId, 
        Core.updXMLFragment ('StatusID', inserted.StatusID, Deleted.StatusID)
        INTO @OutputList

Like I said I get an error when I try the above. I know [$(Database1)] is a legit reference to a database, just need to know how to include this into an OUTPUT statement so that when I move it SSMS, it displays it as [Database1] and not an error.

The reason for [$(Database1)] is so that if we ever change the database name, we don't have to change it in our code as we are using this reference to call on the database rather than the database name itself.

Upvotes: 1

Views: 2400

Answers (2)

Devart
Devart

Reputation: 121902

try to use Dynamic SQL 

DECLARE @Database SYSNAME = 'master'

DECLARE @SQL NVARCHAR(MAX)
SET @SQL = '
USE ' + QUOTENAME(@Database) + '

SELECT DB_NAME()
'

--PRINT @SQL
EXEC sys.sp_executesql @SQL

Upvotes: 1

Kunal Kakkad
Kunal Kakkad

Reputation: 653

One can use sqlcmd Tool to declare the same.

:setvar dbname "TEST" 

CREATE DATABASE $(dbname)
GO
ALTER DATABASE $(dbname) SET COMPATIBILITY_LEVEL = 90
GO
ALTER DATABASE $(dbname) SET RECOVERY SIMPLE 
GO

Upvotes: 1

Related Questions