January 22, 2010 at 10:09 am
------------------------------------------------------------
-- local variables
------------------------------------------------------------
declare @ls_write_line varchar(1000);
declare @ls_table_nm varchar(50);
declare @ls_pk_col_nm varchar(52);
declare @ls_target_db varchar(8);
declare @li_max_id integer;
declare @li_max_value integer;
------------------------------------------------------------
-- cursors and tables
------------------------------------------------------------
declare @table_curs cursor;
declare @a_table table (max_id int);
begin
set @ls_target_db = @in_target_db;
set @table_curs = cursor for select ta.name as table_name
from sys.schemas sc,
sys.tables ta,
sys.columns co
where sc.name = 'varis' and
ta.schema_id = sc.schema_id and
co.object_id = ta.object_id and
co.is_identity = 1
order by ta.name;
open @table_curs;
fetch next from @table_curs into @ls_table_nm;
while @@fetch_status = 0
begin
set @ls_pk_col_nm = @ls_table_nm + 'id';
select max(@ls_pk_col_nm) into @a_table from @ls_table_nm;
select @li_max_value = max_id from @a_table;
end;
January 22, 2010 at 11:33 am
First what is your question?
If it is what I think it is (hmmm) look at Books On Line for the following
SQL Server 2005 Books Online (September 2007)
sys.all_columns (Transact-SQL)
and
sys.columns (Transact-SQL)
From which you can derive the information I think that you are looking for?
If I am incorrect in my assumption please post again with your question
January 22, 2010 at 11:45 am
(Heh heh heh :o))
Apologies for the lack of clarity - you know, so close to the forest that ya can't see the trees....
I guess I have a few 'how to' questions embedded in this post:
1. How can I select a value directly into a variable? Within Oracle or DB2, this is easy, but SQL Server doesn't appear to have this easy capability
2. I have a table name in a variable. How can I execute the following SQL statement? (and get the result value into a variable)
select max(@pk_column_nm) from @table_nm;
January 22, 2010 at 12:40 pm
select max(@pk_column_nm) from @table_nm
This can be done as a dynamic SQL.
About selecting values to a variable...
declare @testvar datetime
Select @testvar = getdate()
-Roy
January 22, 2010 at 12:59 pm
bercea.nick
Apologies for the lack of clarity - you know, so close to the forest that ya can't see the trees
No need for apologies, if you need assistance, help those who want to help you by posting your question following the items listed in the article whose link is in my signature block.
Here is an example of selecting into a user defined variable
DECLARE @Myvariable VARCHAR(50)
SET @Myvariable = (SELECT @@VERSION)
SELECT @Myvariable
Result:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X8
Any additional questions -- post again
January 26, 2010 at 3:32 pm
First of all, my thanks to those who have tried to help. But my problem is a bit nastier than the replys have helped for. My stored procedure accepts a table name on a parm, which resolves correctly. I then derive the primary key column name from the table name, which resolves correctly. But I cannot use the derived names for the table or the primary key column in a SQL statement, and the examples I've seen of retrieving an integer value into a string seem to miss the mark.
Here's some code:
set ansi_nulls on;
set quoted_identifier on;
go
create procedure dbo.sp_techniques (@in_source_db varchar(8), @in_source_schema varchar(8), @in_target_db varchar(8), @in_target_schema varchar(8))
as
------------------------------------------------------------
-- local variables
------------------------------------------------------------
declare @ls_write_line varchar(1000);
declare @ls_source_db varchar(8);
declare @ls_target_db varchar(8);
declare @ls_source_schema varchar(8);
declare @ls_target_schema varchar(8);
declare @ls_table_nm varchar(50);
declare @pk_col_nm varchar(50);
------------------------------------------------------------
-- cursors
------------------------------------------------------------
declare @table_curs_iden cursor;
------------------------------------------------------------
-- entry
------------------------------------------------------------
begin
set @ls_source_db = lower(@in_source_db);
set @ls_source_schema = lower(@in_source_schema);
set @ls_target_db = lower(@in_target_db);
set @ls_target_schema = lower(@in_target_schema);
set @pk_col_nm = @ls_table_nm + 'id'
set @table_curs_iden = cursor for select ta.name as table_name
from sys.schemas sc,
sys.tables ta,
sys.columns co
where sc.name = 'varis' and
ta.schema_id = sc.schema_id and
co.object_id = ta.object_id and
co.is_identity = 1
order by ta.name;
open @table_curs_iden;
fetch next from @table_curs_iden into @ls_table_nm;
while @@fetch_status = 0
begin
set @pk_col_nm = @ls_table_nm + 'id';
set @ls_write_line = 'echo ' + 'table name = ' + @ls_table_nm + ' primary key column name = ' + @pk_col_nm + ' max value = ' + li_max_id_val + '>> c:\schema_copy.dml';
exec master..xp_cmdshell @ls_write_line;
select max(@pk_col_nm) from @ls_table_nm; <-- I cannot get this statement to compile. How to get this value into a variable as well?
fetch next from @table_curs_iden into @ls_table_nm;
end;
close @table_curs_iden;
deallocate @table_curs_iden;
end;
I have a lot of years of Oracle experience, and in Oracle this is a simple problem. The resolution in SQL Server does not appear as straight-forward.
Nick
January 27, 2010 at 5:25 am
Hi,
I guess what's been missing from the help so far is using dynamic sql and assigning output to a variable at the same time.
Assuming you're assigning the max id value to an int variable @li_max_id_val this will work:
SET @sql = 'SELECT @li_max_id_val = MAX(' + @pk_col_nm + ') FROM ' + @ls_table_nm
EXEC sp_executesql @query = @sql, @params = N'@li_max_id_val INT OUTPUT', @li_max_id_val = @li_max_id_val OUTPUT
SET @ls_write_line = 'echo ' + 'table name = ' + @ls_table_nm + ' primary key column name = ' + @pk_col_nm + ' max value = '
+ CONVERT(varchar,@li_max_id_val) + '>> c:\schema_copy.dml'
To understand why this works see this excellent piece:
January 27, 2010 at 6:41 am
This was removed by the editor as SPAM
January 27, 2010 at 2:58 pm
To SSC Rookie -
Thank you! Worked beautifully. I tought dynamic SQL, last night, would be my solution. That syntax is screwy - definitely a SQL Server thing. I'm an Oracle DBA converted to SQL Server, and I'm discovering it is sometimes hard to "unlearn" what I know from the DB2/Oracle world. Why can't Microsoft do things the same way others do? Why do they just have to be different?
To Old Hand -
Thank you for your input too! Very insightful... made me think of a solution to another problem I HAD!!!!!
To Everyone -
This is a great forum. I hope to be contributing imminently! I'm glad I'm here!!!!
Nick
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply