realy need little help - 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

SQL Server Development (2000)

realy need little help

Author

Topic

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-12 : 08:06:14

people what i' am doing wrong ? please help


SET  @SQL=N'SELECT TOP @C_COUNT ID 
		  	FROM DOSKA_OBIAVLENIA 
		  	WHERE RAZDEL_ID=1 
	   ' 	 
		  
EXECUTE SP_EXECUTESQL @SQL,
		      N'@C_COUNT INT',
		        @C_COUNT=1

please dont tell me that i must to write this like

SET @SQL=N'SELECT TOP '+CAST(@C_COUNT AS char)+' ID
FROM DOSKA_OBIAVLENIA
WHERE RAZDEL_ID=1
'

it is wrong (in my situation) !, this @SQL query is query which builded by client

i get syntax error , how to solve this problem

Edited by - marconi8 on 05/12/2003 08:08:29

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-12 : 08:15:49

i'am try more detailed explain my problem

i pass this string into sp

string='select top @X FROM table'

after this in my sp performed this next steps

1) calculate value for @X
2) execute dynamic sql

and i get error 'syntax error', why ?

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-12 : 08:31:28

You'll have to use dynamic sql i'm afraid. you can't use top with a variable, and neither can sp_executesql.

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-12 : 08:45:02

re>you can't use top with a variable

but, hm :(, i dont understand ,

sorry, but i am little nervous, because all what i want to make i always hear answers like = you cannot ,

this is unhappy for me, i think that is very elementar think
maybe i am wrong because i am newbie,

but i dont understand why dynamic sql cannot understand that string @VAR is variable

ok i go to find solution,

thanks for reply

Andraax
Aged Yak Warrior

790 Posts

Posted - 2003-05-12 : 09:00:04

You have to build the string yourself. You cannot use the sp_executesql parameters to do it.

declare @sql varchar(1000)
declare @var int

select @var=10

select @sql = 'select top ' + cast(@var as varchar) + ' * from table'

execute sp_executesql @sql

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-05-12 : 09:07:04

I think he said the CLIENT is building this SQL string, and then passing it into SQL server.

You need to replace the variable with the variable at the CLIENT. SQL has no idea what variables your client has declared or what their values are.

if it's in VB or ASP or something like that:

DIM x as integer
x = 25
SQL = "SELECT TOP " & x & " * FROM Table"

Then later on:

dim r as recordset
set r = Conn.execute(SQL)

or something like that ...

- Jeff

marconi8
Yak Posting Veteran

73 Posts

Posted - 2003-05-12 : 10:42:47

re>You need to replace the variable with the variable at the CLIENT.

people i now , that this variable i can replace on the client and also make something like this ' ' + cast ..... + ' '

but this variable SELECT TOP @X,
@X variable is result of much calculations

solution num. 1 (You need to replace the variable with the variable at the CLIENT ) is wrong ( wrong in my situation )

why wrong ?, because this solution looks like this next

1) query 1 = make calcluations and return vriable
2) query 2 = sending query

you see i must to use 2 query, but my target is to use sp, one sp which do what i need in one cycle, 2 querys decrease speed of needed result,

at this moment i founded only one way

 

ALTER PROCEDURE P_SHOW_LIMITED_OBIAVLENIA
(
 @CUR_POS D_MAIN_ID = 1,
 @SAG D_MAIN_ID,

 @W_STAT NVARCHAR(1000),
 @O_STAT NVARCHAR(1000)
)
AS
DECLARE @TOTAL_COUNT D_MAIN_ID,
		@C_COUNT D_MAIN_ID,
		@SQL_C NVARCHAR(1000),
		@SQL NVARCHAR(1000),
	    @DATA_1 DATETIME,
		@DATA_2 DATETIME
BEGIN
  IF @CUR_POS=0 SET @CUR_POS=1
  SET @DATA_1 = CONVERT(CHAR(8),GETDATE(),112)
  SET @DATA_2 = DATEADD(ss,-1,DATEADD(d,1,@DATA_1))

  CREATE TABLE #TMP_1
  (
   TOTAL_COUNT INT	
  )

  SET @SQL_C='INSERT INTO #TMP_1 
			  SELECT COUNT(ID) 
		      FROM DOSKA_OBIAVLENIA
		  	  WHERE '+@W_STAT

  EXECUTE SP_EXECUTESQL @SQL_C,
					    N'@DATA_1 DATETIME,
						  @DATA_2 DATETIME	
						 ',
						  @DATA_1=@DATA_1,
						  @DATA_2=@DATA_2	


  SET @C_COUNT = (SELECT TOTAL_COUNT FROM #TMP_1) - ((@CUR_POS*@SAG)-@SAG)  
   
  SET @SQL='SELECT TOP '+CAST(@SAG AS CHAR)+' ID,OBIAVLENIE,OBIAVLENIE_TEMA,OBIAVLENIE_TEMA_IMAGE,VISITED FROM DOSKA_OBIAVLENIA '+ 
  		   'WHERE '+@W_STAT+' AND ID IN ( SELECT TOP '+CAST(@C_COUNT AS CHAR)+' ID FROM DOSKA_OBIAVLENIA '+
										  'WHERE '+@W_STAT+
										  'ORDER BY ID DESC ) '+
	       'ORDER BY '+@O_STAT
		   

  EXECUTE SP_EXECUTESQL @SQL,
					    N'@DATA_1 DATETIME,
						  @DATA_2 DATETIME	
						 ',
						  @DATA_1=@DATA_1,
						  @DATA_2=@DATA_2	
    
  RETURN @C_COUNT
END

here is this solution, this solution works very slow, can any one something to advice,

big thanks, if one you have a little time for helping me

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources