Hi
I am trying to populate a Temp table with dynamic SQL but it does not seem
to work and the error message returned is that is it an invalid object,
leading me to believe that it does not get created:
-- ****************************************
********
USE Pubs
GO
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
EXEC (@.SQLString)
SELECT * FROM #TempParamFilter
-- ****************************************
********
Is there something wrong with my syntax?
Kind Regards
RickyThe problem is SCOPE. The # temp table you created goes away after the
dynamic SQL finishes executing. Based on what you posted, you don't even
need dynamic SQL to do this particular job:
USE Pubs
GO
SELECT * INTO #TempParamFilter FROM Authors
SELECT * FROM #TempParamFilter
DROP TABLE #TempParamFilter
"Ricky" <ricky@.msn.com> wrote in message
news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
> Hi
> I am trying to populate a Temp table with dynamic SQL but it does not seem
> to work and the error message returned is that is it an invalid object,
> leading me to believe that it does not get created:
> -- ****************************************
********
> USE Pubs
> GO
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors '
> EXEC (@.SQLString)
> SELECT * FROM #TempParamFilter
>
> -- ****************************************
********
> Is there something wrong with my syntax?
> Kind Regards
> Ricky
>|||Hi Mike
It was a simple example, I should have posted the real situation. What I am
really trying to achieve is a dynamic WHERE clause, appended to a table and
then populate a temporary table. Is this possible.
I mention Dynamic WHERE clause, since the Column may change, depending on
the parameter supplied.
e.g
@.StockParam = 'Q1HTS'
then WHERE clause would be : WHERE QStock = @.StockParam
or
@.StockParam = 'T1HTS'
then WHERE clause would be : WHERE AlphaStock = @.StockParam
so what I thought about doing, was to have my basic select statement and
then append a dynamic WHERE clause as a variable and then populate a #Table
to SELECT from , later when compiling the final recordset.
Hope this makes sense.
Kind Regards
Ricky
"Mike C#" <xyz@.xyz.com> wrote in message
news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> The problem is SCOPE. The # temp table you created goes away after the
> dynamic SQL finishes executing. Based on what you posted, you don't even
> need dynamic SQL to do this particular job:
> USE Pubs
> GO
> SELECT * INTO #TempParamFilter FROM Authors
> SELECT * FROM #TempParamFilter
> DROP TABLE #TempParamFilter
> "Ricky" <ricky@.msn.com> wrote in message
> news:eFBaWbVkGHA.1260@.TK2MSFTNGP05.phx.gbl...
seem
>|||The problem is that the temp table is available only within the scope of
EXEC. Either use a global temp table or use the SELECT within the scope of
the EXEC like:
SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
SELECT * FROM #TempParamFilter '
EXEC (@.SQLString) ;
Anith|||Probably not the most performant, but
WHERE
QStock = CASE @.StockParam
WHEN 'Q1HTS' THEN @.StockParam
ELSE QStock END
AND
AlphaStock = CASE @.StockParam
WHEN 'T1HTS' THEN @.StockParam
ELSE AlphaStock END
What do you need a #temp table for?
"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
> Hope this makes sense.
> Kind Regards
> Ricky
>
>
> "Mike C#" <xyz@.xyz.com> wrote in message
> news:ePpmqeVkGHA.4304@.TK2MSFTNGP03.phx.gbl...
> seem
>|||Hi Anith
How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Kind Regards
Ricky
"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
> The problem is that the temp table is available only within the scope of
> EXEC. Either use a global temp table or use the SELECT within the scope of
> the EXEC like:
> SET @.SQLString = ' SELECT * INTO #TempParamFilter FROM Authors;
> SELECT * FROM #TempParamFilter '
> EXEC (@.SQLString) ;
> --
> Anith
>|||"Ricky" <ricky@.msn.com> wrote in message
news:uZZ7NjVkGHA.3816@.TK2MSFTNGP02.phx.gbl...
> Hi Mike
> It was a simple example, I should have posted the real situation. What I
> am
> really trying to achieve is a dynamic WHERE clause, appended to a table
> and
> then populate a temporary table. Is this possible.
> I mention Dynamic WHERE clause, since the Column may change, depending on
> the parameter supplied.
> e.g
>
> @.StockParam = 'Q1HTS'
> then WHERE clause would be : WHERE QStock = @.StockParam
> or
> @.StockParam = 'T1HTS'
> then WHERE clause would be : WHERE AlphaStock = @.StockParam
> so what I thought about doing, was to have my basic select statement and
> then append a dynamic WHERE clause as a variable and then populate a
> #Table
> to SELECT from , later when compiling the final recordset.
It's possible if you create the temp table before executing the dynamic sql.
Here's an example to get you started. Note that the temp table is created
outside of the dynamic SQL, but the dynamic SQL has access to it. It won't
work the other way around. Also note that this example uses sp_executesql
to parameterize the query. sp_executesql requires NVARCHAR data, and helps
protect against SQL injection:
USE pubs
GO
CREATE TABLE #temp_emp(emp_id VARCHAR(9) NOT NULL PRIMARY KEY,
fname VARCHAR(30) NOT NULL,
minit CHAR(1) NOT NULL,
lname VARCHAR(30) NOT NULL)
DECLARE @.emp_last_name NVARCHAR(30)
SELECT @.emp_last_name = N'Smith'
DECLARE @.dyn_sql NVARCHAR(512)
SELECT @.dyn_sql = N'INSERT INTO #temp_emp (emp_id, fname, minit, lname) ' +
N'SELECT emp_id, fname, minit, lname ' +
N'FROM employee ' +
N'WHERE lname = @.lname'
EXEC dbo.sp_executesql @.dyn_sql, N'@.lname NVARCHAR(30)', @.lname =
@.emp_last_name
SELECT *
FROM #temp_emp
DROP TABLE #temp_emp|||> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
Because a single EXEC() call represents one 'scope' (I'm not sure if that's
a valid noun there, but oh well). The second SELECT is occuring in the same
scope as that which created the #temp table.|||Another way to think about the scope of something like EXEC() is a typical
popup window in a browser (the good kind, not the annoying advertisements).
In most cases, the popup window could jump through several different pages
and do all kinds of things, and the window that opened it couldn't care less
and usually doesn't have any knowledge of what is going on in the popup.
"Ricky" <ricky@.msn.com> wrote in message
news:OPO%23KpVkGHA.1272@.TK2MSFTNGP03.phx.gbl...
> Hi Anith
> How does the second SELECT embedded in the Dynamic SQL overcome the issue?
> Kind Regards
> Ricky
> "Anith Sen" <anith@.bizdatasolutions.com> wrote in message
> news:%23BOJrnVkGHA.1640@.TK2MSFTNGP02.phx.gbl...
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:u4VydtVkGHA.4368@.TK2MSFTNGP03.phx.gbl...
> Because a single EXEC() call represents one 'scope' (I'm not sure if
> that's a valid noun there, but oh well). The second SELECT is occuring in
> the same scope as that which created the #temp table.
You know, I always hated the phrase "well-defined scope" for table
variables, etc. It implies that everything else has "poorly-defined scope"
:) But from a marketing perspective I guess "well-defined scope" sounds
better than "limited scope" or "extremely tight scope" :)
Showing posts with label invalid. Show all posts
Showing posts with label invalid. Show all posts
Wednesday, March 21, 2012
Sunday, March 11, 2012
Dynamically generate table name?
The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
Dynamically generate table name?
The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:
Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegro ups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.
|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/is.../default.aspx.
|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:
Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegro ups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.
|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/is.../default.aspx.
|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>
|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated.
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing.
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
Dynamically generate table name?
The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
[url]http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.[/url
]|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to wh
at
> I'm trying to achieve though - i.e. I want to create a new table which has
a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with nam
e
> "utbl00000006".
> How do I achieve this?
> ===
> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
Dynamically generate table name?
The code below is invalid. However, hopefully it will give a clue as to what
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
--
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
I'm trying to achieve though - i.e. I want to create a new table which has a
name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
the name) from the INSERT statement preceding it e.g. a new table with name
"utbl00000006".
How do I achieve this?
===
CREATE PROCEDURE sp_create_user_table
@.description NVARCHAR(50),
@.username VARCHAR(40)
AS
DECLARE @.tablename VARCHAR(15)
BEGIN TRAN
INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
VALUES (1, @.description, @.username)
SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
CREATE TABLE @.tablename
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)
IF @.@.ERROR <> 0
BEGIN
RAISERROR 50000 'Failed to...'
ROLLBACK TRAN
GOTO end_of_sp
END
COMMIT TRAN
end_of_sp:Why would you ever want to do such a thing? This just looks like an
incredibly bad idea. Could you explain the context please?
If you are trying to record the creation of tables by developers in a
dev environment then look at using a source control system such as MS
SourceSafe. In a production environment however, I can't understand
what use this could have. Permanent tables are expected to be static at
runtime in any business process application and there are very good
reasons why this should be so.
Also, note that you should not use SP_ for user procs. SP_ is reserved
for system procs and should only use used if you intend to create such
a proc in Master.
--
David Portas
SQL Server MVP
--|||MIke,
A few things... I would recommend adding the Identity as a column in your
tblTableMap. @.@.Identity may give you unexpected results. You could format
the leading zeros in the name better by using a select with a case on length
of the identity column. And finally you will need to use dynamic SQL to
issue the create table statement by putting the entire command into a
varchar variable and then use exec(@.variable)
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||Why do you need a table for each user? Why do you need exactly 7 zeros, no
matter what the value of the IDENTITY column becomes? This will yield table
names like:
utbl00000004
utbl0000000567
utbl00000006798
I would think you would want something more like:
utbl00000004
utbl00000567
utbl00006798
Since the identity value generated in tblTableMap is unique, why not just
have a column in a single table and represent all data there? I'm not sure
what you gain by breaking them out into their own tables but still keeping
all of the data in the same database.
As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
What purpose does the tbl prefix serve? Are people not going to be able to
tell that these objects are tables?
For information on other things thatare wrong with your approach, please
see:
http://www.sommarskog.se/dynamic_sql.html
"Mike" <mike@.hello.com> wrote in message
news:%23xtzFNloFHA.764@.TK2MSFTNGP14.phx.gbl...
> The code below is invalid. However, hopefully it will give a clue as to
> what I'm trying to achieve though - i.e. I want to create a new table
> which has a name consisting of "utbl0000000" and the value of @.@.IDENTITY
> (appended to the name) from the INSERT statement preceding it e.g. a new
> table with name "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1124191300.988048.51390@.g43g2000cwa.googlegroups.com...
> Why would you ever want to do such a thing? This just looks like an
> incredibly bad idea. Could you explain the context please?
I understand this could be considered a bad idea, but sorry I don't have the
time nor inclination to explain the reasoning behind it.
I'm purely looking for the syntax that would allow me to define a new table
name based on the contents of a varchar.|||"Danny" <someone@.nowhere.com> wrote in message
news:83kMe.5410$H_4.2360@.trnddc07...
> MIke,
> A few things... I would recommend adding the Identity as a column in your
> tblTableMap. @.@.Identity may give you unexpected results.
Yeah but how do I get the new value of the identity column into the varchar
mentioned below?
>You could format the leading zeros in the name better by using a select
>with a case on length of the identity column.
OK, thanks.
>And finally you will need to use dynamic SQL to issue the create table
>statement by putting the entire command into a varchar variable and then
>use exec(@.variable)
Ahhh... that's what I'm looking for. Great thanks.
I found more related info on it here
http://msdn.microsoft.com/msdnmag/issues/03/04/StoredProcedures/default.aspx.|||still if you stick to dynamic sql this may help you
DECLARE @.tablename varchar(100)
DECLARE @.querystring varchar(200)
SELECT @.tablename = 'ggg0010240'
SELECT @.querystring = 'CREATE TABLE ' + @.tablename + '
(
Unique_ID INT,
Date_Added DATETIME,
Who_Added DATETIME
)'
exec(@.querystring)
Regards
R.D
"Mike" wrote:
> The code below is invalid. However, hopefully it will give a clue as to what
> I'm trying to achieve though - i.e. I want to create a new table which has a
> name consisting of "utbl0000000" and the value of @.@.IDENTITY (appended to
> the name) from the INSERT statement preceding it e.g. a new table with name
> "utbl00000006".
> How do I achieve this?
> ===> CREATE PROCEDURE sp_create_user_table
> @.description NVARCHAR(50),
> @.username VARCHAR(40)
> AS
> DECLARE @.tablename VARCHAR(15)
> BEGIN TRAN
> INSERT INTO tblTableMap (Table_Name, Description, Who_Added)
> VALUES (1, @.description, @.username)
> SELECT @.tablename = 'utbl0000000' + @.@.IDENTITY
> CREATE TABLE @.tablename
> (
> Unique_ID INT,
> Date_Added DATETIME,
> Who_Added DATETIME
> )
> IF @.@.ERROR <> 0
> BEGIN
> RAISERROR 50000 'Failed to...'
> ROLLBACK TRAN
> GOTO end_of_sp
> END
> COMMIT TRAN
> end_of_sp:
>
>|||"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:eC$xXaloFHA.2472@.TK2MSFTNGP15.phx.gbl...
> Why do you need a table for each user?
There isn't a table for each user. The naming is a bit ambiguous I know, but
the tables are user defined.
>Why do you need exactly 7 zeros, no matter what the value of the IDENTITY
>column becomes?
Because I don't forsee more than 99999999 tables being generated. :)
I suppose I could just do utbl1, utbl2, utb1234 couldn't I...
> This will yield table names like:
> utbl00000004
> utbl0000000567
> utbl00000006798
> I would think you would want something more like:
> utbl00000004
> utbl00000567
> utbl00006798
Exactly. I was aware I had to create 9 new tables before worrying about that
sort of thing. :)
> Since the identity value generated in tblTableMap is unique, why not just
> have a column in a single table and represent all data there? I'm not
> sure what you gain by breaking them out into their own tables but still
> keeping all of the data in the same database.
Each table contains completely different types of data.
> As an aside, you should not use @.@.IDENTITY, but rather SCOPE_IDENTITY().
OK, I'll look into it thanks.
> What purpose does the tbl prefix serve? Are people not going to be able
> to tell that these objects are tables?
It is to group my tables together when I view them in Enterprise Manager.
> For information on other things thatare wrong with your approach, please
> see:
> http://www.sommarskog.se/dynamic_sql.html
Interesting read thanks.
Sunday, February 26, 2012
Dynamic use of "inserted" and "deleted" in a trigger
Hello,
Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger?
I get an "invalid object name" with these statement.
set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
EXEC sp_executesql @.sqlcmd
Any other suggestions?
Thanks!
Per>'insert mydatabase.dbo.mytable select * from inserted'
what is dynamic here? No need, If i understand correctly
TRY THIS
INSERT INTO mydatabase.dbo.mytable
SELECT <COLUMN LIST AS IN my table order> from inserted
if schema is different or identity is in the mytables, it throws an error.
if you are expecting something else, do post
Regards
R.D
"Per Buus S?rensen" wrote:
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger
?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||Sorry for the bad example, it should have been:
set @.sqlcmd = 'insert ' + @.db +'.dbo.' + @.table + ' select * from inserted'
EXEC sp_executesql @.sqlcmd
Per
"R.D" wrote:
> what is dynamic here? No need, If i understand correctly
> TRY THIS
> INSERT INTO mydatabase.dbo.mytable
> SELECT <COLUMN LIST AS IN my table order> from inserted
> if schema is different or identity is in the mytables, it throws an error.
> if you are expecting something else, do post
> Regards
> R.D
>
> "Per Buus S?rensen" wrote:
>|||Hi
No you cannot
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a
> trigger?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
as short, concise and efficient as possible because they run in a
transaction.
I find that the best way to create generic trigger code is to generate
it semi-automatically at design-time using the information_schema
views. This is very easy to do as long as the trigger code is identical
or similar in each case. If you prefer you can put your common code in
a stored proc and then insert the contents of the Inserted / Deleted
tables into local temporary table(s) that the proc can use.
Hope this helps.
David Portas
SQL Server MVP
--|||As Portas said, you have to use intermediate temp table because each exec
will have its own scope and inserted table is not in exec() scope.
well if you stick to using dynamic sql for peculiar reasons
use this
create table ##mytemptable<data definition same as inserted table)
insert into ##mytemptable select * from inserted
exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
drop table ##mytemptable
--
if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put N'
before string.
Regards
R.D
"Uri Dimant" wrote:
> Hi
> No you cannot
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
>
>|||The "temp" solution was my first through about a work around, however I dont
know the definition of the source table, since users can change layout from
ERP system, so the trigger should be as general as possiple.
Is it possiple easierly to create a copy of the source table definition as a
temp table?
Per
"R.D" wrote:
> As Portas said, you have to use intermediate temp table because each exec
> will have its own scope and inserted table is not in exec() scope.
> well if you stick to using dynamic sql for peculiar reasons
> use this
> create table ##mytemptable<data definition same as inserted table)
> insert into ##mytemptable select * from inserted
> exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
> drop table ##mytemptable
> --
> if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put
N'
> before string.
> Regards
> R.D
> "Uri Dimant" wrote:
>|||I am aware of the performance issue about such a trigger, but it will be use
d
on smaller master data tables, which updates x other similar tables in other
databases.
Users can setup new databases them self, so flexibility is more important
than performance.
Per
"David Portas" wrote:
> No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
> as short, concise and efficient as possible because they run in a
> transaction.
> I find that the best way to create generic trigger code is to generate
> it semi-automatically at design-time using the information_schema
> views. This is very easy to do as long as the trigger code is identical
> or similar in each case. If you prefer you can put your common code in
> a stored proc and then insert the contents of the Inserted / Deleted
> tables into local temporary table(s) that the proc can use.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Like I suggested before, it sounds like code generation is the way to
go. Much simpler to maintain than dynamic code.
David Portas
SQL Server MVP
--|||I see that the server creates the temp table with correct definition, if it
doesn't exists.
So that solves my problem...so far.
Per
"Per Buus S?rensen" wrote:
> The "temp" solution was my first through about a work around, however I do
nt
> know the definition of the source table, since users can change layout fro
m
> ERP system, so the trigger should be as general as possiple.
> Is it possiple easierly to create a copy of the source table definition as
a
> temp table?
> Per
> "R.D" wrote:
>
Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger?
I get an "invalid object name" with these statement.
set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
EXEC sp_executesql @.sqlcmd
Any other suggestions?
Thanks!
Per>'insert mydatabase.dbo.mytable select * from inserted'
what is dynamic here? No need, If i understand correctly
TRY THIS
INSERT INTO mydatabase.dbo.mytable
SELECT <COLUMN LIST AS IN my table order> from inserted
if schema is different or identity is in the mytables, it throws an error.
if you are expecting something else, do post
Regards
R.D
"Per Buus S?rensen" wrote:
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a trigger
?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||Sorry for the bad example, it should have been:
set @.sqlcmd = 'insert ' + @.db +'.dbo.' + @.table + ' select * from inserted'
EXEC sp_executesql @.sqlcmd
Per
"R.D" wrote:
> what is dynamic here? No need, If i understand correctly
> TRY THIS
> INSERT INTO mydatabase.dbo.mytable
> SELECT <COLUMN LIST AS IN my table order> from inserted
> if schema is different or identity is in the mytables, it throws an error.
> if you are expecting something else, do post
> Regards
> R.D
>
> "Per Buus S?rensen" wrote:
>|||Hi
No you cannot
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
> Hello,
> Is it posiple to user "inserted" and "deleted" in dynamic SQL in a
> trigger?
> I get an "invalid object name" with these statement.
> set @.sqlcmd = 'insert mydatabase.dbo.mytable select * from inserted'
> EXEC sp_executesql @.sqlcmd
> Any other suggestions?
> Thanks!
> Per
>
>|||No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
as short, concise and efficient as possible because they run in a
transaction.
I find that the best way to create generic trigger code is to generate
it semi-automatically at design-time using the information_schema
views. This is very easy to do as long as the trigger code is identical
or similar in each case. If you prefer you can put your common code in
a stored proc and then insert the contents of the Inserted / Deleted
tables into local temporary table(s) that the proc can use.
Hope this helps.
David Portas
SQL Server MVP
--|||As Portas said, you have to use intermediate temp table because each exec
will have its own scope and inserted table is not in exec() scope.
well if you stick to using dynamic sql for peculiar reasons
use this
create table ##mytemptable<data definition same as inserted table)
insert into ##mytemptable select * from inserted
exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
drop table ##mytemptable
--
if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put N'
before string.
Regards
R.D
"Uri Dimant" wrote:
> Hi
> No you cannot
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:BFF8D30E-62D3-4986-B4F5-067601697102@.microsoft.com...
>
>|||The "temp" solution was my first through about a work around, however I dont
know the definition of the source table, since users can change layout from
ERP system, so the trigger should be as general as possiple.
Is it possiple easierly to create a copy of the source table definition as a
temp table?
Per
"R.D" wrote:
> As Portas said, you have to use intermediate temp table because each exec
> will have its own scope and inserted table is not in exec() scope.
> well if you stick to using dynamic sql for peculiar reasons
> use this
> create table ##mytemptable<data definition same as inserted table)
> insert into ##mytemptable select * from inserted
> exec( 'insert ' + @.db +' .dbo. ' + @.table + ' select * from ##mytemptable'
> drop table ##mytemptable
> --
> if you want to use sp_execute sql then define @.sqlcmd as nvarchar and put
N'
> before string.
> Regards
> R.D
> "Uri Dimant" wrote:
>|||I am aware of the performance issue about such a trigger, but it will be use
d
on smaller master data tables, which updates x other similar tables in other
databases.
Users can setup new databases them self, so flexibility is more important
than performance.
Per
"David Portas" wrote:
> No. I wouldn't recommend using dynamic SQL in a trigger. Keep triggers
> as short, concise and efficient as possible because they run in a
> transaction.
> I find that the best way to create generic trigger code is to generate
> it semi-automatically at design-time using the information_schema
> views. This is very easy to do as long as the trigger code is identical
> or similar in each case. If you prefer you can put your common code in
> a stored proc and then insert the contents of the Inserted / Deleted
> tables into local temporary table(s) that the proc can use.
> Hope this helps.
> --
> David Portas
> SQL Server MVP
> --
>|||Like I suggested before, it sounds like code generation is the way to
go. Much simpler to maintain than dynamic code.
David Portas
SQL Server MVP
--|||I see that the server creates the temp table with correct definition, if it
doesn't exists.
So that solves my problem...so far.
Per
"Per Buus S?rensen" wrote:
> The "temp" solution was my first through about a work around, however I do
nt
> know the definition of the source table, since users can change layout fro
m
> ERP system, so the trigger should be as general as possiple.
> Is it possiple easierly to create a copy of the source table definition as
a
> temp table?
> Per
> "R.D" wrote:
>
Friday, February 17, 2012
Dynamic SQL Problem
I'm getting "Invalid Column" error with below code. Can anyone Help?
CODE:
USE [Northwind]
GO
declare @.SQL varchar(1000), @.debug int
declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
int
set @.sTable="Orders"
set @.sField="OrderDate"
set @.sField2="employeeID"
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
--EXEC(@.SQL)Your @.variables are not passed into the Exec(), nor should they be. The
Exec() is run separately from the stored proceedure.
Here is a simple example of how I circumvent this:
declare @.SQL varchar(1000)
declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
set @.getTable='Orders'
set @.getField='OrderDate'
set @.getFilter='employeeID = 3'
SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
Set @.SQL = Replace(@.SQL,'getField',@.getField)
Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
EXEC(@.SQL)
"Scott" wrote:
> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||that's fine except i need a @.employeeID variable. i hardcoded 3 just for
this simple example and will actually be passing 2 WHERE variables in the
production code. can you modify your code?
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:145010B5-D41D-4184-BE78-7F239164BD44@.microsoft.com...
> Your @.variables are not passed into the Exec(), nor should they be. The
> Exec() is run separately from the stored proceedure.
> Here is a simple example of how I circumvent this:
> declare @.SQL varchar(1000)
> declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
> set @.getTable='Orders'
> set @.getField='OrderDate'
> set @.getFilter='employeeID = 3'
> SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
> Set @.SQL = Replace(@.SQL,'getField',@.getField)
> Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
> Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
> EXEC(@.SQL)
>
> "Scott" wrote:
>|||My goof. Your doing pretty much what I am. However, you have double quotes
around you @.variables.
Also watch out for empty space, by declaring as char rather than varchar,
your string is longer than it needs to be.
"Scott" wrote:
> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||You main problem is that you delimited your strings with double quote marks.
In T-SQL, strings are delimited with single quotes. i.e. set @.sTable='Order
s'
For readability of the print @.sql, I changed your variables from char(40) to
varchar(40). The code will execute fine with char(40), it just has a lot of
extra spaces.
If you change the variables to varchar or you happen to have a table name of
40 characters, the WHERE clause will fail because there will be no space
between them. I suggest you put a space in front of the word WHERE as I've
done below.
Finally, EXEC (@.SQL) is no longer the recommended best practices. You
should be using exex sp_executesql which requires unicode input so I changed
@.SQL from varchar(1000) to nvarchar(1000).
Hope that helps,
Joe
Here's the corrected code:
USE [Northwind]
GO
declare @.SQL nvarchar(1000), @.debug int
declare @.sTable varchar(40), @.sField varchar(40), @.sField2 varchar(40),
@.employeeID int
set @.sTable='Orders'
set @.sField='OrderDate'
set @.sField2='employeeID'
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + ' WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
exec sp_executesql @.sql
"Scott" wrote:
> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||Parameterised dynamic queries are best done using the sp_executesql system
procedure. No hassle, no fuss - pure execution.
ML
http://milambda.blogspot.com/
CODE:
USE [Northwind]
GO
declare @.SQL varchar(1000), @.debug int
declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
int
set @.sTable="Orders"
set @.sField="OrderDate"
set @.sField2="employeeID"
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
--EXEC(@.SQL)Your @.variables are not passed into the Exec(), nor should they be. The
Exec() is run separately from the stored proceedure.
Here is a simple example of how I circumvent this:
declare @.SQL varchar(1000)
declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
set @.getTable='Orders'
set @.getField='OrderDate'
set @.getFilter='employeeID = 3'
SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
Set @.SQL = Replace(@.SQL,'getField',@.getField)
Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
EXEC(@.SQL)
"Scott" wrote:
> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||that's fine except i need a @.employeeID variable. i hardcoded 3 just for
this simple example and will actually be passing 2 WHERE variables in the
production code. can you modify your code?
"John Cappelletti" <JohnCappelletti@.discussions.microsoft.com> wrote in
message news:145010B5-D41D-4184-BE78-7F239164BD44@.microsoft.com...
> Your @.variables are not passed into the Exec(), nor should they be. The
> Exec() is run separately from the stored proceedure.
> Here is a simple example of how I circumvent this:
> declare @.SQL varchar(1000)
> declare @.getTable Char(40), @.getField Char(40), @.getFilter varchar(100)
> set @.getTable='Orders'
> set @.getField='OrderDate'
> set @.getFilter='employeeID = 3'
> SET @.SQL = 'SELECT Max([getField]) FROM getTable WHERE getFilter'
> Set @.SQL = Replace(@.SQL,'getField',@.getField)
> Set @.SQL = Replace(@.SQL,'getTable',@.getTable)
> Set @.SQL = Replace(@.SQL,'getFilter',@.getFilter)
> EXEC(@.SQL)
>
> "Scott" wrote:
>|||My goof. Your doing pretty much what I am. However, you have double quotes
around you @.variables.
Also watch out for empty space, by declaring as char rather than varchar,
your string is longer than it needs to be.
"Scott" wrote:
> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||You main problem is that you delimited your strings with double quote marks.
In T-SQL, strings are delimited with single quotes. i.e. set @.sTable='Order
s'
For readability of the print @.sql, I changed your variables from char(40) to
varchar(40). The code will execute fine with char(40), it just has a lot of
extra spaces.
If you change the variables to varchar or you happen to have a table name of
40 characters, the WHERE clause will fail because there will be no space
between them. I suggest you put a space in front of the word WHERE as I've
done below.
Finally, EXEC (@.SQL) is no longer the recommended best practices. You
should be using exex sp_executesql which requires unicode input so I changed
@.SQL from varchar(1000) to nvarchar(1000).
Hope that helps,
Joe
Here's the corrected code:
USE [Northwind]
GO
declare @.SQL nvarchar(1000), @.debug int
declare @.sTable varchar(40), @.sField varchar(40), @.sField2 varchar(40),
@.employeeID int
set @.sTable='Orders'
set @.sField='OrderDate'
set @.sField2='employeeID'
set @.employeeID = 3
set @.debug = 1
SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
SET @.SQL = @.SQL + ' WHERE ' + @.sTable + '.' + @.sField2 + '=' +
CAST(@.employeeID AS VARCHAR(55))
IF @.debug = 1
PRINT @.sql
exec sp_executesql @.sql
"Scott" wrote:
> I'm getting "Invalid Column" error with below code. Can anyone Help?
> CODE:
> USE [Northwind]
> GO
> declare @.SQL varchar(1000), @.debug int
> declare @.sTable Char(40), @.sField Char(40), @.sField2 Char(40), @.employeeID
> int
> set @.sTable="Orders"
> set @.sField="OrderDate"
> set @.sField2="employeeID"
> set @.employeeID = 3
> set @.debug = 1
> SET @.SQL = 'SELECT Max(' + @.sField + ') FROM ' + @.sTable
> SET @.SQL = @.SQL + 'WHERE ' + @.sTable + '.' + @.sField2 + '=' +
> CAST(@.employeeID AS VARCHAR(55))
> IF @.debug = 1
> PRINT @.sql
> --EXEC(@.SQL)
>
>|||Parameterised dynamic queries are best done using the sp_executesql system
procedure. No hassle, no fuss - pure execution.
ML
http://milambda.blogspot.com/
Dynamic SQL Issue
When I execute the following stored procedure I get the
error: 'Invalid operator for data type. Operator equals
subtract, type equals varchar.'
SQL Server thinks I'm trying to subtract the mobile_phone
instead of adding dashes between the numbers.
Here is my stored procedure:
---
create PROCEDURE SelectSortedUsers
@.SortColumn varchar(70)
, @.SortDirection char(4)
AS
declare @.sqlstring varchar(2000);
set @.sqlstring = 'select u.last_name
, u.logon , territory
, r.short_description as Role
, r.role_key
, u.active
, CONVERT(varchar,u.last_login_dt,101) as
last_login_dt
, u.email
, substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) as mobile_phone
from users u
inner join roles r
on u.role_key = r.role_key
order by u.' + @.SortColumn + ' ' + @.SortDirection
exec (@.sqlstring);
=========================================================
How can I add dashes for mobile phone?
Thanks.
DarinYou need to surround strings with single quotes (you will probably see the
problem if you use PRINT @.sql instead of EXEC(@.sql))
, ''' + substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Darin Browne" <db@.nospam.com> wrote in message
news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
> When I execute the following stored procedure I get the
> error: 'Invalid operator for data type. Operator equals
> subtract, type equals varchar.'
> SQL Server thinks I'm trying to subtract the mobile_phone
> instead of adding dashes between the numbers.
> Here is my stored procedure:
> ---
> create PROCEDURE SelectSortedUsers
> @.SortColumn varchar(70)
> , @.SortDirection char(4)
> AS
> declare @.sqlstring varchar(2000);
> set @.sqlstring = 'select u.last_name
> , u.logon , territory
> , r.short_description as Role
> , r.role_key
> , u.active
> , CONVERT(varchar,u.last_login_dt,101) as
> last_login_dt
> , u.email
> , substring(u.mobile_phone, 1, 3) + '-' +
> substring(u.mobile_phone, 4, 3) + '-' +
> substring(u.mobile_phone, 7, 4) as mobile_phone
> from users u
> inner join roles r
> on u.role_key = r.role_key
> order by u.' + @.SortColumn + ' ' + @.SortDirection
> exec (@.sqlstring);
> =========================================================> How can I add dashes for mobile phone?
> Thanks.
> Darin|||Aaron, thanks for your quick reply.
Applying your suggestion, I get an error because Server
doesn't know what table 'u' is aliasing because it's now
outside the dynmaic string where 'u' is aliased.
I've tried moving the 3 quotes around to find the perfect
spot but to no avail.
Any ideas?
Thanks.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>|||It's working!
Thanks for your help.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>
error: 'Invalid operator for data type. Operator equals
subtract, type equals varchar.'
SQL Server thinks I'm trying to subtract the mobile_phone
instead of adding dashes between the numbers.
Here is my stored procedure:
---
create PROCEDURE SelectSortedUsers
@.SortColumn varchar(70)
, @.SortDirection char(4)
AS
declare @.sqlstring varchar(2000);
set @.sqlstring = 'select u.last_name
, u.logon , territory
, r.short_description as Role
, r.role_key
, u.active
, CONVERT(varchar,u.last_login_dt,101) as
last_login_dt
, u.email
, substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) as mobile_phone
from users u
inner join roles r
on u.role_key = r.role_key
order by u.' + @.SortColumn + ' ' + @.SortDirection
exec (@.sqlstring);
=========================================================
How can I add dashes for mobile phone?
Thanks.
DarinYou need to surround strings with single quotes (you will probably see the
problem if you use PRINT @.sql instead of EXEC(@.sql))
, ''' + substring(u.mobile_phone, 1, 3) + '-' +
substring(u.mobile_phone, 4, 3) + '-' +
substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Darin Browne" <db@.nospam.com> wrote in message
news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
> When I execute the following stored procedure I get the
> error: 'Invalid operator for data type. Operator equals
> subtract, type equals varchar.'
> SQL Server thinks I'm trying to subtract the mobile_phone
> instead of adding dashes between the numbers.
> Here is my stored procedure:
> ---
> create PROCEDURE SelectSortedUsers
> @.SortColumn varchar(70)
> , @.SortDirection char(4)
> AS
> declare @.sqlstring varchar(2000);
> set @.sqlstring = 'select u.last_name
> , u.logon , territory
> , r.short_description as Role
> , r.role_key
> , u.active
> , CONVERT(varchar,u.last_login_dt,101) as
> last_login_dt
> , u.email
> , substring(u.mobile_phone, 1, 3) + '-' +
> substring(u.mobile_phone, 4, 3) + '-' +
> substring(u.mobile_phone, 7, 4) as mobile_phone
> from users u
> inner join roles r
> on u.role_key = r.role_key
> order by u.' + @.SortColumn + ' ' + @.SortDirection
> exec (@.sqlstring);
> =========================================================> How can I add dashes for mobile phone?
> Thanks.
> Darin|||Aaron, thanks for your quick reply.
Applying your suggestion, I get an error because Server
doesn't know what table 'u' is aliasing because it's now
outside the dynmaic string where 'u' is aliased.
I've tried moving the 3 quotes around to find the perfect
spot but to no avail.
Any ideas?
Thanks.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>|||It's working!
Thanks for your help.
>--Original Message--
>You need to surround strings with single quotes (you
will probably see the
>problem if you use PRINT @.sql instead of EXEC(@.sql))
>, ''' + substring(u.mobile_phone, 1, 3) + '-' +
>substring(u.mobile_phone, 4, 3) + '-' +
>substring(u.mobile_phone, 7, 4) + ''' as mobile_phone
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Darin Browne" <db@.nospam.com> wrote in message
>news:28ef01c3af95$7d02a8a0$a601280a@.phx.gbl...
>> When I execute the following stored procedure I get the
>> error: 'Invalid operator for data type. Operator equals
>> subtract, type equals varchar.'
>> SQL Server thinks I'm trying to subtract the
mobile_phone
>> instead of adding dashes between the numbers.
>> Here is my stored procedure:
>> ---
>> create PROCEDURE SelectSortedUsers
>> @.SortColumn varchar(70)
>> , @.SortDirection char(4)
>> AS
>> declare @.sqlstring varchar(2000);
>> set @.sqlstring = 'select u.last_name
>> , u.logon , territory
>> , r.short_description as Role
>> , r.role_key
>> , u.active
>> , CONVERT(varchar,u.last_login_dt,101) as
>> last_login_dt
>> , u.email
>> , substring(u.mobile_phone, 1, 3) + '-' +
>> substring(u.mobile_phone, 4, 3) + '-' +
>> substring(u.mobile_phone, 7, 4) as mobile_phone
>> from users u
>> inner join roles r
>> on u.role_key = r.role_key
>> order by u.' + @.SortColumn + ' ' + @.SortDirection
>> exec (@.sqlstring);
=========================================================>> How can I add dashes for mobile phone?
>> Thanks.
>> Darin
>
>.
>
Dynamic SQL Invalid Column name error
Any idea why this is telling me that the field value I want to pass in
is an invalid column name?
This is for a "Search By" query. I have a drop down of choices and a
text box for the value. I need to search for the value entered in the
text box in the drop down field. The list is populated from fields in
more than 1 table.
STORED PROC:
ALTER PROCEDURE dbo.searchQuery2
(
@.searchTxt varchar(50) = NULL, @.searchField varchar(50)
)
AS
Declare @.sql varchar(4000)
Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
r.dateSubmitted, a.Name
From Request r, AppComm a
Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
a.badgeID'
Select @.sql
exec (@.sql)
OUTPUT:
Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
a.Name
From Request r, AppComm a
Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
Invalid column name 'a111111'.
(1 row(s) returned)
@.RETURN_VALUE = 0
Finished running dbo."searchQuery2".
Why is it returning the correct SQL but telling me that a111111 is a
column? - it is not a column at all, it is the value. I have tried
playing around with single and double quotes but nothing seems to work.
Please help.
Thanks!Because the value a11111 is a string, and you need to enclose string in sing
le quotes:
> Where ' + @.searchField + ''' = ' + @.searchTxt + ''' and r.reviewedBy =
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Shenoy.D@.gmail.com> wrote in message news:1147981295.111755.280880@.j73g2000cwa.googlegroup
s.com...
> Any idea why this is telling me that the field value I want to pass in
> is an invalid column name?
> This is for a "Search By" query. I have a drop down of choices and a
> text box for the value. I need to search for the value entered in the
> text box in the drop down field. The list is populated from fields in
> more than 1 table.
> STORED PROC:
> ALTER PROCEDURE dbo.searchQuery2
> (
> @.searchTxt varchar(50) = NULL, @.searchField varchar(50)
> )
> AS
> Declare @.sql varchar(4000)
> Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
> r.dateSubmitted, a.Name
> From Request r, AppComm a
> Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
> a.badgeID'
> Select @.sql
> exec (@.sql)
>
> OUTPUT:
> Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
> a.Name
> From Request r, AppComm a
> Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
>
> Invalid column name 'a111111'.
> (1 row(s) returned)
> @.RETURN_VALUE = 0
> Finished running dbo."searchQuery2".
> Why is it returning the correct SQL but telling me that a111111 is a
> column? - it is not a column at all, it is the value. I have tried
> playing around with single and double quotes but nothing seems to work.
> Please help.
> Thanks!
>
is an invalid column name?
This is for a "Search By" query. I have a drop down of choices and a
text box for the value. I need to search for the value entered in the
text box in the drop down field. The list is populated from fields in
more than 1 table.
STORED PROC:
ALTER PROCEDURE dbo.searchQuery2
(
@.searchTxt varchar(50) = NULL, @.searchField varchar(50)
)
AS
Declare @.sql varchar(4000)
Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
r.dateSubmitted, a.Name
From Request r, AppComm a
Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
a.badgeID'
Select @.sql
exec (@.sql)
OUTPUT:
Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
a.Name
From Request r, AppComm a
Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
Invalid column name 'a111111'.
(1 row(s) returned)
@.RETURN_VALUE = 0
Finished running dbo."searchQuery2".
Why is it returning the correct SQL but telling me that a111111 is a
column? - it is not a column at all, it is the value. I have tried
playing around with single and double quotes but nothing seems to work.
Please help.
Thanks!Because the value a11111 is a string, and you need to enclose string in sing
le quotes:
> Where ' + @.searchField + ''' = ' + @.searchTxt + ''' and r.reviewedBy =
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<Shenoy.D@.gmail.com> wrote in message news:1147981295.111755.280880@.j73g2000cwa.googlegroup
s.com...
> Any idea why this is telling me that the field value I want to pass in
> is an invalid column name?
> This is for a "Search By" query. I have a drop down of choices and a
> text box for the value. I need to search for the value entered in the
> text box in the drop down field. The list is populated from fields in
> more than 1 table.
> STORED PROC:
> ALTER PROCEDURE dbo.searchQuery2
> (
> @.searchTxt varchar(50) = NULL, @.searchField varchar(50)
> )
> AS
> Declare @.sql varchar(4000)
> Select @.sql = 'Select r.requestID, r.projectManager, r.projectName,
> r.dateSubmitted, a.Name
> From Request r, AppComm a
> Where ' + @.searchField + ' = ' + @.searchTxt + 'and r.reviewedBy =
> a.badgeID'
> Select @.sql
> exec (@.sql)
>
> OUTPUT:
> Select r.requestID, r.projectManager, r.projectName, r.dateSubmitted,
> a.Name
> From Request r, AppComm a
> Where sumbittedBy = a111111 and r.reviewedBy = a.badgeID
>
> Invalid column name 'a111111'.
> (1 row(s) returned)
> @.RETURN_VALUE = 0
> Finished running dbo."searchQuery2".
> Why is it returning the correct SQL but telling me that a111111 is a
> column? - it is not a column at all, it is the value. I have tried
> playing around with single and double quotes but nothing seems to work.
> Please help.
> Thanks!
>
Subscribe to:
Posts (Atom)