November 9, 2007 at 9:13 am
:ermm:
Hi im very new to Dynamic SQL to thank you for bearing with me.
I have to create a view that will be created dynamically everyweek depending the tables that are still present in the db. There are 13 tables invovled and union all between them.
I will have to look up sysojbects to lookup tables that are still present with a specific naming convention.
so far i can get a list of tables with a rownumber. at one time there are going to be 13 tables present but the last two digits in the name will change almost regularly.
--CODE TO GET THE 13 TABLES PRESENT WITH ROWNUMBERS
Select top 13 ROW_NUMBER() Over(Order By s1.id) As RowNumber, name
From dbo.sysobjects as s1
where name like 'check%'
I also have working code of creating a view from a variable..but dont know what to do from here..
I would prefer not to use a cursor but then how can i look up a rownumber and put the value of the table in the variable plug that in the view and then go back and do the same thing over again 13 times?
Please help and thanks in advance
November 9, 2007 at 9:35 am
You can't use dynamic SQL in a view, since you would need EXEC or sp_executesql to execute the SQL, and a view definition can only consist of a SELECT statement. What you could do, though, is build some dynamic SQL that would drop and recreate the view. The problem with using the view then would be that it wouldn't "know" when your underlying schema had changed: you'd need to run your dynamic SQL after each change to the schema.
An even better alternative would be to change the design of your database... is that an option?
John
November 9, 2007 at 9:57 am
I can create a view using a variable below..
declare @newname varchar(60), @sql nvarchar(1500)
set @newname = 'Checks' + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE())) + '_1'
set @sql = 'CREATE View testview AS
select CUID, Amount,Sequence ,RetDate from '+@newname+''
Exec (@sql)
im trying to insert into a temp table the names along with rownumber which is NOT WORKING.
I know it was gonna be long and probably not the best way to go about this but i would have written the above code 13 times and set the variable @newname to a new table name everytime. what im trying to do is
use a temp table or create a table which could take the table names from sysobjects and increment them some how..
CREATE TABLE #Tabs(rownumber int, name varhcar(1400)
Insert into #Tabs (rownumber, name)
Select top 100 ROW_NUMBER() Over(Order By c1.id) As RowNumber, name
From dbo.sysobjects as c1
where name like 'check%'
so the result table could be like
Rownum Table
1 check10
2 check 11
3 check 12
and so on..
so i can set the variable @newname
set @newname = select table where rownum = 1 and then next code change rownum from 1 to 2.
is this making any sense. or should I go about this a totally different way.
November 9, 2007 at 10:04 am
Maybe something like this:
DECLARE @SQL nvarchar(4000)
DECLARE @counter int
SET @SQL='ALTER VIEW VW_MYVIEW AS ' + CHAR(13) + CHAR(10)
SET @counter = 0
Select top 13 @counter = @counter + 1, @SQL = @SQL + 'SELECT * FROM ' + name + CHAR(13) + CHAR(10) + CASE WHEN @counter=13 THEN '' ELSE ' UNION ALL' + CHAR(13) + CHAR(10) END
From dbo.sysobjects as s1
WHERE name like 'check%'
order by id
EXEC sp_executesql @SQL
You may need to alter the above to get the same 13 tables as your earlier SQL - I didn't have the tables to properly test that
Using ALTER VIEW requires the view to be there for the first time - but save re-applying rights to the view as ALTER VIEW maintains the rights that the view already had (as opposed to DROP & CREATE which would require you to reassign rights)
I do also agree it would be better not to have to do this - i.e. I assume your tables are CHECKnn named - so why can't you just have table CHECK with a new int column in it that takes the value nn ?
James Horsley
Workflow Consulting Limited
November 9, 2007 at 10:13 am
Thank you for your help.
This a third party application, so nothing can be changed and they create a new table in the yyyymm format everymonth but if the application hangs sometimes ti can take up to 5 weeks to create a new table, so i need a script that i can put in as a job to run every week to rebuild or alter the view dynamically.
November 13, 2007 at 8:25 am
Thank you for your help. The code worked but could you please tell me what the CHAR(13) + CHAR(10) are used for and can the length of the fields be changed?
Select top 13 @counter = @counter + 1, @SQL = @SQL + 'SELECT * FROM ' + name + CHAR(13) + CHAR(10) + CASE WHEN @counter=13 THEN '' ELSE ' UNION ALL' + CHAR(13) + CHAR(10) END
November 13, 2007 at 10:48 am
nabaj,
char(13) = carriage return
char(10) = line feed
This basically like hitting the enter key to create a new line or a line break.
November 14, 2007 at 2:51 am
Thanks for answering for me Adam - I have also PM'd the answer - I never really considered it before but I guess it is confusing that CHAR() is both a datatype and a function
James Horsley
Workflow Consulting Limited
November 14, 2007 at 7:11 am
Thank you very much for ur explaination
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply