assign a variable

Question

Post reply

assign a variable

mick miller

SSChasing Mays

Points: 631
More actions
July 1, 2009 at 12:48 am

#206118

Hi
How I can assign a variable?
I would 'sum' the mdf-files from a database and assign to a variable.
Is that somehow possible:
@size_mdf = EXEC sp_executesql @sql_size_mdf
or like row: 5 with execute the sp_execute in a select?
0 DECLARE @size_mdf int
1 DECLARE @sql_size_mdf nvarchar(100)
2 SET @sql_size_mdf = 'select SUM(size)*8/1024 from [master].sys.database_files where type = 0'
3 EXEC sp_executesql @sql_size_mdf
4 DECLARE @sql nvarchar(1000)
5 SET @sql = 'select
6,(EXEC sp_executesql @sql_size_mdf)
7,convert(datetime,(convert(int,(convert(float,getdate())))))
8from [srsql1].[master].sys.database_files'
9 EXEC sp_executesql @sql
Thanks
Miller

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

t.hitendra SSCommitted Points: 1620 More actions · Answer 1

Hi Mick,

As far as I can understand you need to assign the variable

for that you might try this

DECLARE @size_mdf int
DECLARE @sql_size_mdf nvarchar(100)
SET @sql_size_mdf = 'select @size_mdf=SUM(size)*8/1024 from [master].sys.database_files where type = 0'
EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output
select @size_mdf

t.hitendra SSCommitted Points: 1620 More actions · Answer 2

Hi Mick,

As far as I can understand you need to assign the variable

for that you might try this

DECLARE @size_mdf int
DECLARE @sql_size_mdf nvarchar(100)
SET @sql_size_mdf = 'select @size_mdf=SUM(size)*8/1024 from [master].sys.database_files where type = 0'
EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output
select @size_mdf

t.hitendra SSCommitted Points: 1620 More actions · Answer 3

Hi Mick,

As far as I can understand you need to assign the variable

for that you might try this

DECLARE @size_mdf int
DECLARE @sql_size_mdf nvarchar(100)
SET @sql_size_mdf = 'select @size_mdf=SUM(size)*8/1024 from [master].sys.database_files where type = 0'
EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output
select @size_mdf

t.hitendra SSCommitted Points: 1620 More actions · Answer 4

Hi Mick,

As far as I can understand you need to assign the variable

for that you might try this

DECLARE @size_mdf int
DECLARE @sql_size_mdf nvarchar(100)
SET @sql_size_mdf = 'select @size_mdf=SUM(size)*8/1024 from [master].sys.database_files where type = 0'
EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output
select @size_mdf

t.hitendra SSCommitted Points: 1620 More actions · Answer 5

Hi Mick,

As far as I can understand you need to assign the variable

for that you might try this

DECLARE @size_mdf int
DECLARE @sql_size_mdf nvarchar(100)
SET @sql_size_mdf = 'select @size_mdf=SUM(size)*8/1024 from [master].sys.database_files where type = 0'
EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output
select @size_mdf

t.hitendra SSCommitted Points: 1620 More actions · Answer 6

Hi Mick,

As far as I can understand you need to assign the variable

for that you might try this

DECLARE @size_mdf int
DECLARE @sql_size_mdf nvarchar(100)
SET @sql_size_mdf = 'select @size_mdf=SUM(size)*8/1024 from [master].sys.database_files where type = 0'
EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output
select @size_mdf

mick miller SSChasing Mays Points: 631 More actions · Answer 7

maybe think so...!?

I will the result from:

@size_mdf = SET @sql_size_mdf = 'select SUM(size)*8/1024 from [master].sys.database_files where type = 0'

in this select row 6:

4 DECLARE @sql nvarchar(1000)

5 SET @sql = 'select

6 ,@_size_mdf

7 ,convert(datetime,(convert(int,(convert(float,getdate())))))

8 from [srsql1].[master].sys.database_files'

In the Attachement is the hole script, but it isn't run, because I can't wright into a variable:

- line 41 and 45 isn't correct!

- line 52, 53, 54 is the variable

Thanks

Mick

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 8

Hi mick

please have a look to t.hitendra's snippet. You need to fill your variables inside your dynamic SQL and declare them as output parameter by calling "sp_executesql"

Notice the bold text:

EXEC sp_executesql @sql_size_mdf,N'@size_mdf int output',@size_mdf output