Dymanic select problem - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Dymanic select problem

Author

Topic

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 15:55:19

CREATE PROCEDURE abc @flnm varchar(40)
AS
Declare @y varchar(40),
@x varchar(40),
@sq varchar(255),
@cnt int
set @x = @flnm
Set @y='abc_' + @x
set @sq=
'Select'+ @cnt+'=count(*) from '+ @y
exec(@sq)
when Iam executing the above proc iave this error
Syntax error converting the varchar value 'Select' to a column of data type int.
Can someone fix this error

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-02 : 16:09:41

It doesn't work that way...

Here...count everything


CREATE table #TEMP (TBNAME sysname,ColCount int)
go

DECLARE @cmd varchar(255) 

DECLARE Space_csr CURSOR READ_ONLY FOR 
	SELECT 'INSERT INTO #TEMP SELECT '
		+''''+'['+TABLE_SCHEMA+']'
		+ '.'
		+'['+TABLE_NAME+']'+''''
		+',COUNT(*) FROM '
		+'['+TABLE_SCHEMA+']'
		+'.'
		+'['+TABLE_NAME+']'
	  FROM INFORMATION_SCHEMA.Tables
	 WHERE TABLE_TYPE = 'BASE TABLE'

SET NOCOUNT ON

OPEN Space_csr

FETCH NEXT FROM Space_csr
	INTO	@cmd

WHILE @@FETCH_STATUS = 0

	BEGIN
		EXEC(@cmd)
		FETCH NEXT FROM Space_csr
			INTO	@cmd

	END

SELECT * FROM #TEMP WHERE ColCount <> 0 Order by ColCount DESC

SELECT * FROM #TEMP WHERE ColCount = 0 Order by TBNAME

CLOSE Space_csr
DEALLOCATE Space_csr

DROP TABLE #TEMP
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 16:28:34

AsimKhaliq, your last few questions have all been related to dynamic sql. I think that you should really rethink your design. Dynamic sql requires explicit table permissions instead of just execute on the stored procedure. Dynamic sql also does not perform as well as explicity statements. You really need to think about your design and determine if this is the way to go as this is so not recommended.

To answer your question, you just need to CONVERT @cnt to VARCHAR to get it to work. Look up CONVERT in SQL Server Books Online for the details.

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 16:28:52

I did not get ur point
If iwould have query like
Select count(*) from tablename then its ok
But in my case I have two thing one the table name is dynamic @y coming form input parameter, the other thing I want my count(*) result in a local vaiable so i can manipulate something. I am ok with table name and used the query many places but problem is @cnt=count(*), caz my query is dynamic. Can u plz tell me that how I will fix this sytex if possible.

Thanks in advance

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 16:32:09

CONVERT is the answer to your question.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-02 : 16:34:44

quote:
Originally posted by tduggan

CONVERT is the answer to your question.

Tara

Tara,

He can't do this


'Select'+ @cnt+'=count(*) from '+ @y

You need to put in to a table and get it out afterwards...

...kinda like the code I posted...

Is what you're building for adminstration or an end user app?

Or a college course?

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 16:36:54

Ah yes. Not sure how to solve this one though. Dynamic sql is not the way to go for the design though.

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-02 : 16:39:34

quote:
Originally posted by tduggan

Ah yes. Not sure how to solve this one though. Dynamic sql is not the way to go for the design though.

Tara

Look at my code? No other way...

I forget who I got that from...

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 16:44:05

But that counts everything like you said. How about one dynamic table? If you don't know which table it is, then how do you know which one to get from the temporary table?

Tara

X002548
Not Just a Number

15586 Posts

Posted - 2003-12-02 : 16:51:50

ohh...


USE Northwind
GO

CREATE table #TEMP (TBNAME sysname,ColCount int)
GO

DECLARE @cmd varchar(255), @TABLE_NAME sysname, @TABLE_SCHEMA sysname

SELECT @TABLE_NAME = 'Order Details', @TABLE_SCHEMA = 'dbo' 

	SELECT @cmd =  'INSERT INTO #TEMP SELECT '
		+''''+'['+@TABLE_SCHEMA+']'
		+ '.'
		+'['+@TABLE_NAME+']'+''''
		+',COUNT(*) FROM '
		+'['+@TABLE_SCHEMA+']'
		+'.'
		+'['+@TABLE_NAME+']'
EXEC(@cmd)

SELECT * FROM #TEMP

DROP TABLE #TEMP
GO

Brett

8-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 16:54:39

Well that's the answer then AsimKhaliq. These kinds of problems exist with dynamic sql. Why do you need dynamic tables in the first place? You said in another thread because you need to enter dynamic ones in, but why do you need to enter them in? What kind of system is this?

Tara

AsimKhaliq
Yak Posting Veteran

94 Posts

Posted - 2003-12-02 : 17:22:33

I want Dynamic Table caz every time i will get different files and i have to make different tables for same client
What I forgot to mention is I will provide my filenAME through My procedurre parameter i.e @flnm and will make a new table, which ihad already done

Now My procedures will be dynamic it will accept the filename and update the table for specific file. The above procedure is a part of my big logic, which consists of many tasks.

set @sq='Select'+ @cnt+'=CONVERT(varchar(10),count(*)) from '+ @y

is the write query and I dont have any error now. Form Above query my result is null, though i have data in my table
and when iam using print @cnt its empty

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-12-02 : 17:27:39

You need to use Brett's solution in order for it to work. What I had said about CONVERT would normally work except not for dynamic sql.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources