December 8, 2005 at 8:13 am
Hello,
I was trying to run the following procedure, I'm not getting the output, I'm not sure if the syntax especially the qoutes around the parameters to be correct. Is it a good way to use the dyamic Sql or Is there other options.
Please if you can correct the syntax.
CREATE proc test_it @param1 int = null,
@param2 varchar(10) = null,
@param3 varchar(10) = null,
@param4 varchar(10) = null,
@param5 varchar(10) = null,
@param6 varchar(10) = null,
AS
BEGIN
declare @case varchar(200)
declare @sql varchar(8000)
select @case = codes from Table1 where col1= @param1
select @sql= 'select col2,col3, col4, col5, col6,cast (sum(col8)as decimal(9,2))as test from table2
where (col2 between '+ @param2+' and'+ @param3+ ')
and col3= '+@param3+ ' and col4= '+@param4+ ' and
col5 in('+ @case + ') and col6= '+@param6+'
group by col2,col3, col4, col5, col6'
EXEC (@sql)
end
Thanks
December 8, 2005 at 8:32 am
If you are using QA (Query Analyzer), it should be fairly easy to identify all the syntax errors...when I ran it through the parser, the only error I got was the comma after "@param6" & before "AS"...
1) Have you tried executing this with actual values instead of variables ?!
2) As for "other options" you would first have to outline what your goals are before someone can help you with alternative solutions..
**ASCII stupid question, get a stupid ANSI !!!**
December 8, 2005 at 9:58 am
Thanks for reply.IF the @param2 will be Integer is the qoutations around the variables will be different compre to the variables. BEcause when I replaced with the actual values it brings back data.
Also the procedure is compiled succefully. But with the execution and passing parameters it will give an error
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting the varchar value 'select col2,col3, col4, col5, col6,cast (sum(col8)as decimal(9,2))as test from table2
where (col2 between ' to a column of data type smallint.
Thanks
December 8, 2005 at 10:12 am
sarwa99 - it'd be much easier for someone to help you if you provide the DDL of your table along with some sample data and required results.
It seems that your "col2" is of data type smallint and you're trying to filter it based on varchar(10) parameters.
Also, you would be much better off using 0 (zero) & '' (space) for defaults rather than 'Null' - since you would have to do an "IS NULL" comparison and not an " = NULL" if some of those parameters do not contain values....
**ASCII stupid question, get a stupid ANSI !!!**
December 8, 2005 at 10:46 am
I think the sintax is wrong in the declaration of the last parameter, it shouldnt be a "," after the last parameter in the creation of the sp and try no to put null in the parameters, it not usefull at all.
December 9, 2005 at 4:42 am
Your @param variables are all varchars so in order to concatenate them try the following and see what happens:
SELECT @sql= 'SELECT col2,col3, col4, col5, col6, CAST (SUM (col8) AS decimal(9,2)) AS test
FROM table2
WHERE col2 BETWEEN ''' + @param2 + ''' AND '''+ @param3 + '''
AND col3 = '''+ @param3+ '''
AND col4 = ''' + @param4 + '''
AND col5 IN (''' + @case + ''')
AND col6 = ''' + @param6 + '''
GROUP BY col2, col3, col4, col5, col6'
December 9, 2005 at 2:59 pm
What's happening is one or more of the columns you are trying to filter on a character columns and you are generating the dynamic sql as if all the parameters you wanted to compare were integers. In this case, there is probably one column that is defined as smallint and your param is an alpha character. Utilizing the suggested code from diogenes1331 you should be able to get your code to run.
If you are still having issues, please give us the schema of "table2" and the parameters you are passing to this stored procedure.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply