question

sindhu avatar image
sindhu asked

Dynamic sql

I need to create table dynamically.The columns are getting from the [dbo].[ws_flat_table_config_Recognition]. I have a client where we won't be getting any columns from @CreateColumns and @CreateColumns2 but will have columns for @CreateColumns1 My create table script doesn't work.I am not getting any error but it doesn't create or print. If I remove @CreateColumns and @CreateColumns2 from the create table script and run.It works. Please help DECLARE @Company_id int = 264 DECLARE @CreateColumns NVARCHAR(MAX) = (Stuff((Select ' ,uexf_'+ Convert(Varchar, field_name) +' '+Column_Data_Type From [dbo].[ws_flat_table_config_Recognition] where company_id = @company_id and type = 'USER' and source = 'USER_EXTRA_FLAT' order by col_sort for xml path('')), 1, 2, '')) DECLARE @CreateColumns1 NVARCHAR(MAX) = (Stuff((Select ' ,uex_'+ Convert(Varchar, field_name) +' '+Column_Data_Type From [dbo].[ws_flat_table_config_Recognition] where company_id = @company_id and type = 'USER' and source = 'USER_EXTRA' order by col_sort for xml path('')), 1, 2, '')) DECLARE @CreateColumns2 NVARCHAR(MAX) = (Stuff((Select ' ,cms_'+ Convert(Varchar, field_name) +' '+Column_Data_Type From [dbo].[ws_flat_table_config_Recognition] where company_id = @company_id and type = 'USER' and source = 'CMS' order by col_sort for xml path('')), 1, 2, '')) DECLARE @CreateTable nVARCHAR(MAX) = 'CREATE TABLE dbo.ws_user_flat1_'+ cast(@Company_id as varchar(20)) + ' (id INT,company_employee_id varchar(500),first_name varchar(500),last_name varchar(500),email varchar(500),address1 varchar(500) ,address2 varchar(500),city varchar(100),state varchar(100),zip varchar(50),status_id INT,statusList_Name varchar(500),is_active bit, '+ @CreateColumns+' , '+ @CreateColumns1+' , '+ @CreateColumns2+' , IS_deleted bit default(0) )' Print @CreateTable ---Exec sp_executeSQL @CreateTable
sql
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

gm_ignat avatar image
gm_ignat answered
What returns @CreateColumns and @CreateColumns2 ? But @CreateColumns1 ?
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image
Kev Riley answered
I suspect that you have NULL values for those variables where there are no corresponding columns. Does changing the query to this, print OK? DECLARE @CreateTable nVARCHAR(MAX) = 'CREATE TABLE dbo.ws_user_flat1_'+ cast(@Company_id as varchar(20)) + ' (id INT,company_employee_id varchar(500),first_name varchar(500),last_name varchar(500),email varchar(500), address1 varchar(500),address2 varchar(500),city varchar(100),state varchar(100),zip varchar(50),status_id INT, statusList_Name varchar(500),is_active bit,' + isnull(@CreateColumns,'')+' , '+ isnull(@CreateColumns1,'')+' , '+ isnull(@CreateColumns2,'') +' , IS_deleted bit default(0) )'
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Mart avatar image
Mart answered
Hi Sindhu It's likely that the queries, when not returning any data, pass NULL to your variable. To get around this there are, as I see it, two approaches (actually three but I'll come back to that). The first is simply to use the ISNULL function around the @CreateColulmns(x) to replace it with an empty string, here's an example: --declare the variables needed DECLARE @CreateColumns NVARCHAR(MAX) , @CreateColumns1 NVARCHAR(MAX) , @CreateColumns2 NVARCHAR(MAX) --set a value for the variables (in yours this would be a select so would presumably return NULL if there is not data SET @CreateColumns = NULL SET @CreateColumns1 = 'Column2 varchar(30)' SET @CreateColumns2 = NULL --a little bit of checking on route PRINT 'CreateColumns : ' + ISNULL(@CreateColumns,'') PRINT 'CreateColumns1: ' + ISNULL(@CreateColumns1,'') PRINT 'CreateColumns2: ' + ISNULL(@CreateColumns2,'') You have this returned: CreateColumns : CreateColumns1: Column2 varchar(30) CreateColumns2: Without the ISNULL you'd have this, notice that the lines that are concatenated with the NULL are not there: CreateColumns1: Column2 varchar(30) The second option is to build up the statement with the variables appended as you go. This is a longer approach but, for more complex code, much easier to work with, IMHO. Using the same as above we can then add the building blocks below to get the full statement (I've kept it close to yours but tweaked slightly to allow testing: DECLARE @CreateColumns NVARCHAR(MAX) , @CreateColumns1 NVARCHAR(MAX) , @CreateColumns2 NVARCHAR(MAX) --set a value for the variables (in yours this would be a select so would presumably return NULL if there is not data SET @CreateColumns = NULL SET @CreateColumns1 = 'Column2 varchar(30)' SET @CreateColumns2 = NULL --a little bit of checking on route PRINT 'CreateColumns : ' + @CreateColumns PRINT 'CreateColumns1: ' + @CreateColumns1 PRINT 'CreateColumns2: ' + @CreateColumns2 PRINT CHAR(13) --extra declaration for as I haven't got a company_id DECLARE @Company_id int = 1 --your statement with the end trimmed and the table made temp DECLARE @CreateTable nVARCHAR(MAX) = 'CREATE TABLE #ws_user_flat1_'+ cast(@Company_id as varchar(20)) + ' (id INT,company_employee_id varchar(500),first_name varchar(500),last_name varchar(500),email varchar(500),address1 varchar(500) ,address2 varchar(500),city varchar(100),state varchar(100),zip varchar(50),status_id INT,statusList_Name varchar(500),is_active bit' --I'll assume you'll have NULL from the query, this could be changed to an empty string instead if needed. --Append the extra columns IF @CreateColumns IS NOT NULL BEGIN SET @CreateTable = @CreateTable + ', ' + @CreateColumns END IF @CreateColumns1 IS NOT NULL BEGIN SET @CreateTable = @CreateTable + ', ' + @CreateColumns1 END IF @CreateColumns2 IS NOT NULL BEGIN SET @CreateTable = @CreateTable + ', ' + @CreateColumns2 END --Now add final closing part SET @CreateTable = @CreateTable + ', IS_deleted bit default(0) )' --and print statement again PRINT @CreateTable And here's the statement that's produced: CREATE TABLE #ws_user_flat1_1 (id INT,company_employee_id varchar(500),first_name varchar(500),last_name varchar(500),email varchar(500),address1 varchar(500) ,address2 varchar(500),city varchar(100),state varchar(100),zip varchar(50),status_id INT,statusList_Name varchar(500),is_active bit, Column2 varchar(30), IS_deleted bit default(0) ) I like the above approach as it can be applied to more options based on different query results and different IF statements. The third option, which will still work, but should be avoided as it's deprecated is to use a session side setting that allows concatenation of NULLS. The option is SET CONCAT_NULL_YIELDS_NULL OFF [ https://devondba.blogspot.co.uk/2012/11/concatnullyieldsnull.html][1] This simply prevents the NULL value 'breaking' the concatenation of the strings, but as you can see it's not breaking it, it's simply yielding NULL when a NULL is concatenated with another string. Here's the example and the output: SET CONCAT_NULL_YIELDS_NULL OFF --declare the variables needed DECLARE @CreateColumns NVARCHAR(MAX) , @CreateColumns1 NVARCHAR(MAX) , @CreateColumns2 NVARCHAR(MAX) --set a value for the variables (in yours this would be a select so would presumably return NULL if there is not data SET @CreateColumns = NULL SET @CreateColumns1 = 'Column2 varchar(30)' SET @CreateColumns2 = NULL --a little bit of checking on route PRINT 'CreateColumns : ' + @CreateColumns PRINT 'CreateColumns1: ' + @CreateColumns1 PRINT 'CreateColumns2: ' + @CreateColumns2 CreateColumns : CreateColumns1: Column2 varchar(30) CreateColumns2: So it returns the same as if the NULL value variables had the ISNULL function around them. My recommendation would be not to use it as it's deprecated (although still in at the moment) and use one of the other options depending on your preference. [1]: https://devondba.blogspot.co.uk/2012/11/concatnullyieldsnull.html
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.