SELECT returns records with no columns

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)

SELECT returns records with no columns

Author

Topic

mayhem
Starting Member

11 Posts

Posted - 2004-02-29 : 06:11:08

I posted a question yesterday about how to SELECT different groups of data from one table into several columns [url]http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=32907[/url]. I think i've figured that out. now, i'm having problems returning the query! After running the procedure, which uses dynamic sql, I got a table with 23 records (expected), but no columns! So as an experiment, i proceeded to change the query so that the records are selected into a new table, and I do get what i want. So what is wrong? i read somewhere about setting nocount on, and i tried that too but to no avail.
The codes are quite long but anyway i include them below.


ALTER PROCEDURE dbo.tryA
( @Array varchar(1000)
  ) 
AS
set nocount on
declare @separator_position int -- This is used to locate each separator character
declare @array_value varchar(1000) -- this holds each array value as it is returned
declare @W int
DECLARE @SQL1 varchar(3000)
DECLARE @SQL2 varchar(3000)
DECLARE @SQL3 varchar(1000)
DECLARE @SQL varchar(8000)
DECLARE @i varchar(20)
DECLARE @p int
SET @W=0
SET @p=0
set @array = @array + ','
SET @SQL3 = ' WHERE (sP.Class = ''03S33'')'
while patindex('%' + ',' + '%' , @array) <> 0 
begin
  select @separator_position =  patindex('%' + ',' + '%' , @array)
  select @array_value = left(@array, @separator_position - 1)
  select @array = stuff(@array, 1, @separator_position, '')
SET @i=LTRIM(RTRIM(STR(@p)))
IF @p=0
BEGIN
SET @SQL1='SELECT t0.StudentId, sp.STUDENT_NAME, t0.Assmt_Marks As c0'
SET @SQL2=' FROM dbo.MA_test_Results t0 INNER JOIN dbo.studentProfile sP on t0.StudentId=sP.student_nric AND t0.Assmt_Id='+@array_value
END
ELSE
BEGIN
SET @SQL1= RTRIM(@SQL1)+',t'+@i+'.Assmt_Marks As c'+@i
SET @SQL2= RTRIM(@SQL2)+' INNER JOIN dbo.MA_test_Results t'+@i+' ON t'+@i+'.StudentId=t0.StudentId AND t'+@i+'.Assmt_Id='+@array_value
END
SET @p=@p+1

end

SET @SQL1=RTRIM(@SQL1)+' INTO marks'
SET @SQL=RTRIM(@SQL1)+RTRIM(@SQL2)+RTRIM(@SQL3)
EXEC(@SQL)
SET nocount off

SamC
White Water Yakist

3467 Posts

Posted - 2004-03-01 : 00:41:58

Could you rewrite this query using DYNAMIC WHERE?

SQLTeam has a couple of articles on this. You're query will read easier and be easier to diagnose.

If you choose not to rewrite your query, how about printing out a couple of the SQL command strings and repost? You may decipher what is wrong yourself if you do.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources