How to store user value into variable

[Former Member]

Hi all,

I'm struggling with SQL queries and variables.

Is there a way to get value from user and store it to variable? I need to do some checking/validation to the user values before passing them to main query.

For example:

 DECLARE @myvar AS int
 SET @myvar = [%0]        

 IF (@myvar < 100)
 BEGIN
   SET @myvar = 100
 END

 SELECT * FROM OCRD T0 WHERE T0.CardCode>@myvar

SQL Server errors:

1). [Microsoft][ODBC SQL Server Driver][SQL Server]Must specify table to select from. 
2). [Microsoft][ODBC SQL Server Driver][SQL Server]Statement 'Search Function' (s) could not be prepared.

Kind regards,

Jani

[AlexGrebennikov · Accepted solution]

Jani, 1st what you have to bear in mind is that [%0] and $-syntax are internal SBO features, so it won't work in Query Analizer. To test there your query you have to replace such simbols with exact values.

2nd, you have to implement some tricks to make your query executable inside SBO.

Here is a snippet how your query can be altered:

DECLARE @myCardCode AS nVarChar(8)
/* SELECT T0.CardCode FROM OCRD T0 WHERE */ SET @myCardCode /* T0.CardCode */ = N'[%0]'
IF (@myCardCode < N'C100')
     SET @myCardCode = N'C100'

SELECT T0.* FROM OCRD T0 WHERE T0.CardCode > @myCardCode

HTH

[Former Member · Accepted solution]

Great! Thanks a lot for your help.

I'm just wondering is SAP going to fix this 'thing'... I mean it would be much easier for all of us if we could use these [%0] variables directly.

Anyway, my problem is now solved. Thanks again! :]