select with parametric name of table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

select with parametric name of table

Author

Topic

mosquito1270
Starting Member

6 Posts

Posted - 2009-08-04 : 05:18:20

Hi,
I've another problem about use of a select with parametric name of table.

I'd like to use this code

set @tabla_n_coste= ' N_COSTES_'+@Fecha
select @sum_cos_coste_total=sum(cos_coste_total) from @tabla_n_coste where cos_ser_id=@SerId

but doesn't run :(
I believe that I do some sintax error, because I write

select @sum_cos_coste_total=sum(cos_coste_total) from NAME_200909 where cos_ser_id=@SerId

It's ok.

I know that I can use also this sintax

set @sql=' select sum(cos_coste_total) as sum_cos_coste_total ' + ' '
set @sql=@sql + ' from ' + @tabla_n_coste + ' where cos_ser_id = '+ @SerId

exec(@sql)

But after, I don't know how can call the "sum_cos_coste_total" variable

As I wrote yesterday in another thread/post, I've just started to use Sql*Server, so I'm sorry for
those simples questions.

thank's for any suggestion
Ciao
Paolo

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 05:28:06

Hi Mosquito270.

Please read this for all questions you might have for dynamic SQL.

Generally, try really hard to stay away from dynamic SQL. It's not often the best solution. In fact it's almost always a *bad* solution.

Good article link:
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

mosquito1270
Starting Member

6 Posts

Posted - 2009-08-04 : 05:38:29

quote:
Originally posted by Transact Charlie

Hi Mosquito270.

Please read this for all questions you might have for dynamic SQL.

Generally, try really hard to stay away from dynamic SQL. It's not often the best solution. In fact it's almost always a *bad* solution.

Good article link:
http://www.sommarskog.se/dynamic_sql.html

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Ok, thank's! I'm studying it.
But do you think, if can I try in this document the solution to my problem?
Actually I don't find it nothing to help me :(

ciao

mosquito1270
Starting Member

6 Posts

Posted - 2009-08-04 : 07:26:51

I'm trying to use sp_executesql statment, but I haven't succes, because now I've a error to convert from nvarchar to numeric.

This is the code

declare @sum_cos_coste_total decimal(28,8)
declare @tabla_n_coste varchar(200)
declare @nsql nvarchar(4000)
declare @outputVal nvarchar(30)

set @tabla_n_coste= ' N_COSTES_'+@Fecha

select @nsql= N'select '+@sum_cos_coste_total+'=sum(cos_coste_total) from '+@tabla_n_coste+' where cos_ser_id='+@SerId

exec sp_executesql
@stmt = @nsql
,@params = N'cast(@sum_cos_coste_total AS nvarchar) decimal(28,8) out'
,@retVal = @outputVal out

select @outputVal

Thank's for any suggestions

Ciao

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 07:56:36

You haven't understood the exec sp_executeSql call. I suggest you read the article carefully and try to understand it.

I think what you were aiming for wias this:

(I've assumed that you actually want to return a DECIMAL(28,8) value. Alos that @SerId is an INT / BIGINT and that @Fecha is a VARCHAR/NVARCHAR.


DECLARE @nsql NVARCHAR(MAX)
DECLARE @tabla_n_coste NVARCHAR(200)
DECLARE @outputVal DECIMAL(28,8) 

SET @tabla_n_coste = N'N_COSTES_' + @Fecha

SET @nsql = N'
	SELECT
		@sum_cos_coste_total = SUM([cos_coste_total])
	FROM
		' + @tablea_n_coste + '
	WHERE
		[cos_ser_Id] = @SerId '

EXEC sp_executeSql
	@nsql

	,N'@sum_cos_coste_total DECIMAL(28,8) OUTPUT
	, @SerId INT'

	, @outputVal OUTPUT
	, @SerId

SELECT @outputVal

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 07:59:03

Now -- *why* do you want to do things this way? Do you really not know which table you will be querying?

Also -- you need to make sure that @Fecha doesn't contain any bad characters -- you are opening yourself up to sql injection (read the article) which could be a big problem.

It might be a good idea to explain what you are actually wanting to do. This doesn't look like a good idea to me,

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 08:03:58

Do you have different TABLES per period?

The example you posted was "NAME_200909"

Do you maybe have a different table for each month?

This is a really, really bad idea. What stage is your application / whatever development at? Can this be changed?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2009-08-04 : 10:03:51

Charlie, maybe you just don't understand Enterprise Development™

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-04 : 10:34:13

When that is a synonym for "broken mess" yes, you are right.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources