running a result set...

Last Post 04 Jun 2012 11:05 AM by new2sql. 7 Replies.
AddThis - Bookmarking and Sharing Button
Author Messages
new2sql
New Member
New Member

--
11 May 2012 06:28 AM
i have a query that will produce a single script to change NOT NULL to NULL for
the purpose of an import, and some other modifications.

my question is how do i get the result set to run in sql 2008?

so once i have a concat result; how do i get it to run automatically? for example
in a stored procedure.


here's the result set i'm trying to run:

alter table dbo.MyTable alter column MyColumn01 smallint null
alter table dbo.MyTable alter column MyColumn01 datetime null
alter table dbo.MyTable alter column MyColumn01 datetime null
alter table dbo.MyTable alter column MyColumn01 datetime null
alter table dbo.MyTable alter column MyColumn01 datetime null
alter table dbo.MyTable alter column MyColumn01 char null
alter table dbo.MyTable alter column MyColumn01 varchar null
alter table dbo.MyTable alter column MyColumn01 varchar null
alter table dbo.MyTable alter column MyColumn01 varchar null
alter table dbo.MyTable alter column MyColumn01 smallint null
alter table dbo.MyTable alter column MyColumn01 char null
alter table dbo.MyTable alter column MyColumn01 datetime null
alter table dbo.MyTable alter column MyColumn01 char null
alter table dbo.MyTable alter column MyColumn01 datetime null
alter table dbo.MyTable alter column MyColumn01 char null

any ideas would be greatly appreciated.

thanks
new2sql
New Member
New Member

--
11 May 2012 06:29 AM
by the way it's not all the same column name... just changed them to column1 as an example.
rm
New Member
New Member

--
11 May 2012 08:17 AM
You may need dynaimc sql here.
new2sql
New Member
New Member

--
11 May 2012 08:55 AM
hmm...

forgive my ignorance guys, but i took this script:

/********************************************/
/********************************************/

select 'alter table ' + s.name+'.'+o.name + ' alter column ' +c.name + ' '+ t.name +' null'
from sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type='u' and o.name = 'MyTable'
and c.isnullable=1

/********************************************/
/********************************************/

then put it into dynamic sql like this:

declare @TableName varchar(20)
declare @Run_Results nvarchar(2000)

set @TableName = 'MyTable'
set @Run_Results =

'
select ''alter table '' + s.name+''.''+o.name + '' alter column '' +c.name + '' ''+ t.name +'' null''
from sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type=''u'' and o.name = ' + @TableName + '
and c.isnullable=1
'

select @Run_Results -- to see what would actually be run instead of: exec (@Run_Results)

/********************************************/
/********************************************/

-- the actual results were simply the script it's self.

elect 'alter table ' + s.name+'.'+o.name + ' alter column ' +c.name + ' '+ t.name +' null'
from sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type='u' and o.name = MyTable
and c.isnullable=1

/********************************************/
/********************************************/

-- i suppose what i need to do is put the actual results into @results nvarchar(1000)
-- this do something like this at the end: exec (@results)
/********************************************/
/********************************************/


my question is how does one do that? put multiple result sets into a variable? i thought
that wasn't allowed.



new2sql
New Member
New Member

--
11 May 2012 09:02 AM
i tried it this way:

/***************************************/
/***************************************/

declare @TableName varchar(20)
declare @Run_Results varchar(2000)

set @TableName = 'MyTable'
set @Run_Results =
(
select 'alter table ' + s.name+'.'+o.name + ' alter column ' +c.name + ' '+ t.name +' null'
from sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type='u' and o.name = @TableName
and c.isnullable=1
)

select (@Run_Results)

/***************************************/
/***************************************/

it gave me this error:

ERROR:
Subquery returned more than 1 value.
This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

so... should i try to coalesce, or something like that, or is there something easy i can do?
new2sql
New Member
New Member

--
11 May 2012 09:13 AM
ok... so i took the liberty of running this with COALESCE

it worked!! you see thats all i needed. just a couple pro's telling me what i might need to do. guys MANY THANKS


/*********************************************************/
/*********************************************************/


declare @TableName varchar(20)
declare @NotNull_Script nvarchar(2000)

set @TableName = 'MyTable'

select @NotNull_Script = coalesce(@NotNull_Script + ' alter table ' + s.name+'.'+o.name + ' alter column ' +c.name + ' '+ t.name +' null;', '')
from sys.objects as o
join sys.schemas as s on s.schema_id=o.schema_id
join sys.syscolumns as c on o.name=object_name(c.id)
join sys.systypes as t on c.usertype=t.usertype
where o.type='u' and o.name = @TableName
and c.isnullable=1

select @NotNull_Script


/*********************************************************/
/*********************************************************/
rm
New Member
New Member

--
12 May 2012 11:09 AM
You can run it with sp_executesql @NotNull_Script or exec (@NotNull_Script), books online has details.
new2sql
New Member
New Member

--
04 Jun 2012 11:05 AM
you guys are great!


Acceptable Use Policy
---