Creating table using dynamic SQL

I have the following dataset using dynamic SQL which works when i execute it, when i try to create a table using this dataset i cant see any fields. Does anyone know why dynamic SQL doesnt work ?

Declare @.TopRange int
Declare @.BottomRange int
Declare @.SQL Varchar(1000)

IF @.Param_leadage = '91+'
SET @.TopRange = 91
ELSE
Set @.TopRange = RTRIM(LEFT(REPLACE(@.Param_leadage,'-',''),2))

IF @.Param_leadage = '91+'
SET @.BottomRange = 4000
ELSE
Set @.BottomRange = LTRIM(RIGHT(REPLACE(@.Param_leadage,'-',''),2))

SET @.SQL = 'SELECT dbo.tblCustomer.idStatus, dbo.tblCustomer.idCustomer, dbo.tblCustomer.DateSigned' +
' FROM dbo.tblCustomer' +
' WHERE DateDiff(day, dbo.tblCustomer.DateSigned, GetDate()) >= ' + convert(varchar,@.TopRange) + ' AND DateDiff(day,dbo.tblCustomer.DateSigned, GetDate()) <= ' + convert(varchar,@.BottomRange)

IF @.Param_status = 'Online Churn'
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = 4 or dbo.tblCustomer.idStatus = 5 or dbo.tblCustomer.idStatus = 11'
ELSE
SET @.SQL = @.SQL + ' AND dbo.tblCustomer.idStatus = ' + @.Param_idstatus

EXEC(@.SQL)

If the Refresh Fields toolbar button enabled on the Data tab is enabled, try clicking it to get the fields. Or, plug in only the SELECT statement and hit Refresh Fields to get the fields.Then, change it to the original SQL. Finally, you can create the dataset fields manually.

|||

Hi Teo,

This isnt working, when i try to select a data field from the table textbox properties value dropdown box, it says 'Dataset1 has no fields'

When I enter it in manually it gives an error which is obviously to do with the fields not being in the scope.

In the Data screen within VSS 2005 the sql executes fine, when i refresh it prompts for parameters off which i enter and the data is returned, it is not recognising any fields when i try to access any fields from any controls..

it has something to do with the exec(sql) command and dynamic data i would suspect

I also tried doing the SQL first off which the table fields are recognised but as soon as i add the other portions such as the single quotes etc it gives the same error

Can someone please help

thanks

|||

Try putting this code in a stored proc and changing the command type to be stored proc.

The other option is to not use dynamic SQL, instead use an expression based query and construct the SQL using expression syntax e.g.

= Iif(Parameters!x.value = "91+", "Select...", "select ...")

Again, in this case I follow the previous advice of just putting in the SQL query first, hitting refresh so RS auto-generates the Filed definitions and then swapping in the expression

|||

@. is interpreted as a query parameter in the Query Designer. You can make the whole darn thing expression-based as Adam suggested or move this query to a stored procedure.

|||

thanks guys, i put it in a stored proc and it is working now