Showing posts with label master. Show all posts
Showing posts with label master. Show all posts

Monday, March 26, 2012

How to use value of a variable in defining data type

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :

DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER

--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.

Please suggest

Thanks in Advance

Quote:

Originally Posted by aviansh

HI Experts,

I have same table structures in two database and one master table which contains Table id, Table name,primary key, data type of primary key. i have to comapare
Tables in both tha database and as per result i have to do insert,update or delete.

for that i have written query :


DECLARE @.rowcount_mastertable FLOAT
SET @.rowcount_mastertable = (select count(*) from master_table)

DECLARE @.TABLE_ID float,
@.TABLE_NAME varchar (100),
@.primary_key varchar (100),
@.Primarykey_DATATYPE varchar (50),

DECLARE @.COUNTER FLOAT
SET @.COUNTER = 1

WHILE (@.Counter <= @.rowcount_mastertable)

Begin

SET @.TABLE_NAME = (SELECT TABLE_NAME FROM MASTER_TABLE TABLE_ID = @.COUNTER)
SET @.primary_key = (SELECT primary_key FROM MASTER_TABLE WHERE TABLE_ID = @.COUNTER)
SET @.Primarykey_DATATYPE = (SELECT Primarykey_DATATYPE FROM MASTER_TABL WHERE TABLE_ID = @.COUNTER)

--In below line i want to declare a variable and datatype should be same as what we got from master table so that i can use this @.MAX_primary_key to fetch max of primary key from table name where table id is 1

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE
SELECT @.MAX_primary_key = MAX(@.primary_key) FROM @.TABLE_NAME
WHERE TABLE_ID = @.COUNTER


--But by running it i am getting error that "Incorrect syntax near '@.Primarykey_DATATYPE'. and "Must declare the variable '@.MAX_primary_key'.


Please suggest

Thanks in Advance



Examine this line more closely...

DECLARE @.MAX_primary_key @.Primarykey_DATATYPE

at what point are you declaring either of these variable datatypes?

Jim :)

how to use use <db name> in store procedure

Hi
I am passing database name as a parameter in the stored procedure
I want to create stored procedure in master and want to access the database
systables depending on the input to the stored procedure.
How do I go in that specific database as I can not use "use @.dbname"
in store procedure.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I am passing database name as a parameter in the stored procedure
> I want to create stored procedure in master and want to access the
> database systables depending on the input to the stored procedure.
> How do I go in that specific database as I can not use "use @.dbname"
> in store procedure.
> Thanks
> Mangesh
The easiest way is probably to use dynamic SQL. Just be really careful
of SQL Injection issues. Make sure the procedure does adequate data
validation.
Creating procedures in master is not recommended. For one reason, they
may disappear with service pack installations. I think you're better off
creating a shared database to store all your "shared" procedures.
Microsoft does this in SQL Server with the msdb database. It just
requires using the database name when executing the procedure.
-- Dynamic SQL
Declare @.n nvarchar(1000)
Declare @.db nvarchar(128)
Set @.db = N'[pubs]'
Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
Exec sp_executesql @.n
David Gugick
Imceda Software
www.imceda.com|||Thanks David.
Is there a best practices document you know of for coding in sqlserver?
"David Gugick" wrote:
> Mangesh Deshpande wrote:
> > Hi
> >
> > I am passing database name as a parameter in the stored procedure
> > I want to create stored procedure in master and want to access the
> > database systables depending on the input to the stored procedure.
> >
> > How do I go in that specific database as I can not use "use @.dbname"
> > in store procedure.
> >
> > Thanks
> > Mangesh
> The easiest way is probably to use dynamic SQL. Just be really careful
> of SQL Injection issues. Make sure the procedure does adequate data
> validation.
> Creating procedures in master is not recommended. For one reason, they
> may disappear with service pack installations. I think you're better off
> creating a shared database to store all your "shared" procedures.
> Microsoft does this in SQL Server with the msdb database. It just
> requires using the database name when executing the procedure.
>
> -- Dynamic SQL
> Declare @.n nvarchar(1000)
> Declare @.db nvarchar(128)
> Set @.db = N'[pubs]'
> Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
> Exec sp_executesql @.n
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>sql

how to use use <db name> in store procedure

Hi
I am passing database name as a parameter in the stored procedure
I want to create stored procedure in master and want to access the database
systables depending on the input to the stored procedure.
How do I go in that specific database as I can not use "use @.dbname"
in store procedure.
Thanks
Mangesh
Mangesh Deshpande wrote:
> Hi
> I am passing database name as a parameter in the stored procedure
> I want to create stored procedure in master and want to access the
> database systables depending on the input to the stored procedure.
> How do I go in that specific database as I can not use "use @.dbname"
> in store procedure.
> Thanks
> Mangesh
The easiest way is probably to use dynamic SQL. Just be really careful
of SQL Injection issues. Make sure the procedure does adequate data
validation.
Creating procedures in master is not recommended. For one reason, they
may disappear with service pack installations. I think you're better off
creating a shared database to store all your "shared" procedures.
Microsoft does this in SQL Server with the msdb database. It just
requires using the database name when executing the procedure.
-- Dynamic SQL
Declare @.n nvarchar(1000)
Declare @.db nvarchar(128)
Set @.db = N'[pubs]'
Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
Exec sp_executesql @.n
David Gugick
Imceda Software
www.imceda.com
|||Thanks David.
Is there a best practices document you know of for coding in sqlserver?
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> The easiest way is probably to use dynamic SQL. Just be really careful
> of SQL Injection issues. Make sure the procedure does adequate data
> validation.
> Creating procedures in master is not recommended. For one reason, they
> may disappear with service pack installations. I think you're better off
> creating a shared database to store all your "shared" procedures.
> Microsoft does this in SQL Server with the msdb database. It just
> requires using the database name when executing the procedure.
>
> -- Dynamic SQL
> Declare @.n nvarchar(1000)
> Declare @.db nvarchar(128)
> Set @.db = N'[pubs]'
> Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
> Exec sp_executesql @.n
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>

how to use use <db name> in store procedure

Hi
I am passing database name as a parameter in the stored procedure
I want to create stored procedure in master and want to access the database
systables depending on the input to the stored procedure.
How do I go in that specific database as I can not use "use @.dbname"
in store procedure.
Thanks
MangeshMangesh Deshpande wrote:
> Hi
> I am passing database name as a parameter in the stored procedure
> I want to create stored procedure in master and want to access the
> database systables depending on the input to the stored procedure.
> How do I go in that specific database as I can not use "use @.dbname"
> in store procedure.
> Thanks
> Mangesh
The easiest way is probably to use dynamic SQL. Just be really careful
of SQL Injection issues. Make sure the procedure does adequate data
validation.
Creating procedures in master is not recommended. For one reason, they
may disappear with service pack installations. I think you're better off
creating a shared database to store all your "shared" procedures.
Microsoft does this in SQL Server with the msdb database. It just
requires using the database name when executing the procedure.
-- Dynamic SQL
Declare @.n nvarchar(1000)
Declare @.db nvarchar(128)
Set @.db = N'[pubs]'
Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
Exec sp_executesql @.n
David Gugick
Imceda Software
www.imceda.com|||Thanks David.
Is there a best practices document you know of for coding in sqlserver?
"David Gugick" wrote:

> Mangesh Deshpande wrote:
> The easiest way is probably to use dynamic SQL. Just be really careful
> of SQL Injection issues. Make sure the procedure does adequate data
> validation.
> Creating procedures in master is not recommended. For one reason, they
> may disappear with service pack installations. I think you're better off
> creating a shared database to store all your "shared" procedures.
> Microsoft does this in SQL Server with the msdb database. It just
> requires using the database name when executing the procedure.
>
> -- Dynamic SQL
> Declare @.n nvarchar(1000)
> Declare @.db nvarchar(128)
> Set @.db = N'[pubs]'
> Set @.n = N'Select * from ' + @.db + N'.[dbo].[sysobjects]'
> Exec sp_executesql @.n
>
> --
> David Gugick
> Imceda Software
> www.imceda.com
>