How to execute a dynamic SQL with integer parameter for stored procedure?

I'm having problem on trying to execute a query in stored procedure that has parameters as a integer. The parameter with the integer is in the WHERE clause. If I take out the WHERE clause, it would work. If I take out the parameter and replace it with a value, it would work. I have try using the CONVERT function to convert it to an integer, still no luck.

Error: Unterminated String Constant.

What is the problem?

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By '+ @.SortExpression+') as Row_Count,Rank() Over (Order By '+ @.SortExpression+') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between '+@.startRowIndex+' and '+ @.maxRowIndex+' ';
Exec(@.strSQL);

Can you please try:

Where Row_Count Between ' +Convert(Varchar,@.startRowIndex)+' and '+Convert(Varchar,@.maxRowIndex)+''

|||

Try

Set @.strSQL='Select *
From
(
SELECT Row_Number() Over(Order By ' + @.SortExpression + ') as Row_Count,Rank() Over (Order By ' + @.SortExpression + ') as TableInfo_ColumnSort,dbo.EVENT_LOGS.EVENTLOG_ID, dbo.USERS.USERNAME, dbo.EVENT_LOGS.ITEM_TYPE, dbo.EVENT_LOGS.SCREEN_ID,
dbo.EVENT_LOGS.CHANGE_TYPE, dbo.EVENT_LOGS.IP_ADDRESS, dbo.EVENT_LOGS.CREATE_DATE,dbo.USERS.FIRST_NAME,dbo.USERS.Last_NAME
FROM dbo.EVENT_LOGS INNER JOIN
dbo.USERS ON dbo.EVENT_LOGS.USER_UID = dbo.USERS.USERID
) as TableInfo
Where Row_Count Between ' +CONVERT(VARCHAR(10),@.startRowIndex) -- the integer needs to be converted to varchar for the + to be a concatenate!
+ ' and ' +CONVERT(VARCHAR(10),@.maxRowIndex) + ' ';
PRINT @.strSQL -- Comment this out once the TSQL is generated correctly
Exec(@.strSQL);

|||

It works! I was converting it to an integer with the convert function. I didn't realized I had to convert it as a string..

how to exec a stored procedure

hi,

how do I exec stored procedure that accept parameter and return a single value?

here is example of report

stu_id = ******

stu_name = ****

subject | marks

aa****** | call sp_mark and return student mark for that particular student id and subject

bb****** | call sp_mark and return student mark for that particular student id and subject

cc****** | call sp_mark and return student mark for that particular student id and subject

thks,

You cannot call a stored procedure per row if you mean that with your mentioned design, you would have to get all the information within one procedure to display it in the bound table.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Charles,

Have you tried using a user defined function in place of the stored procedure?

Simone

|||

A potentially better performing alternative to a user defined function would probably be a derived table containing the marks for each student by subject. You would then join on the table.

Something like

select stu_id, stu_name, subject, mark

from students s

left outer join (select stu_id, subject, marks from marks) m on m.stu_id = s.stu_id

Of course you would need to summarize the marks into a table...

cheers,

Andrew

How to enter a date parameter in Debugger

(This is prob. a really dumb question but it's driving me mad!!...)

I am using the Debugger in SQL Query Analyzer & want to set the value of a datetime parameter prior to executing the stored proc. The "Debug procedure" window allows me to specify the parameter values - but I can't get it to accept a datetime. The language is us_english & I've tried most ways if specifying the date - 01/02/2004, with/out quotes, 02 Jan 2004, as a full datetime, swapping day/month values etc etc. The procedure always fails immediately with: Invalid character value for cast specification.

Thanks.Got it ... finally!

yyyy-mm-dd

Argggggh.

how to draw a straight line in the chart?

I want to draw a straight line whose parameter can be set in a
variaty.how to get it?If this is a line chart or a column chart, you could just add another data
series and plot that series as line (in case of a column chart, select the
"plot data as line" checkbox on the appearance tab). Set the data value
expression a parameter-dependent value, e.g. =Parameters!Threshold.Value and
set the BorderColor property
accordingly.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spirit" <qmxlf@.163.com> wrote in message
news:1130427418.066195.117940@.g47g2000cwa.googlegroups.com...
>I want to draw a straight line whose parameter can be set in a
> variaty.how to get it?
>|||select '2' as constant from .........
do you mean like that?|||You could also do it by using expressions. Attached a sample report at the
bottom of this posting which has a dynamic target line in the chart.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Spirit" <qmxlf@.163.com> wrote in message
news:1130601362.110856.78230@.g49g2000cwa.googlegroups.com...
> select '2' as constant from .........
> do you mean like that?
============================================================
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="TotalSalesByYear">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style />
<Legend>
<Visible>true</Visible>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<Color>Brown</Color>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Pastel</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BackgroundGradientEndColor>Black</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>Blue</BackgroundColor>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderColor>
<Default>Yellow</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Size>6pt</Size>
</Marker>
</DataPoint>
</DataPoints>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=(Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15</Value>
</DataValue>
</DataValues>
<Style>
<BorderWidth>
<Default>6pt</Default>
</BorderWidth>
<BorderColor>
<Default>=iif((Sum(Fields!UnitPrice.Value *
Fields!Quantity.Value)+8000)*1.15 > 100000, "Aqua", "Green")</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Marker>
<Type>Diamond</Type>
<Size>10pt</Size>
<Style>
<BackgroundColor>Yellow</BackgroundColor>
</Style>
</Marker>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=iif(Year(Fields!OrderDate.Value) = 1996, 75000,
iif(Year(Fields!OrderDate.Value) = 1997, 90000, 115000))</Value>
</DataValue>
</DataValues>
<DataLabel />
<Style>
<BorderWidth>
<Default>10pt</Default>
</BorderWidth>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</DataPoint>
</DataPoints>
<PlotType>Line</PlotType>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title>
<Style />
</Title>
<Style>
<Format>MM/yyyy</Format>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Margin>true</Margin>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>100</PointWidth>
<Type>Column</Type>
<Title>
<Caption>Sales / Cost / Target</Caption>
<Style>
<FontSize>14pt</FontSize>
<FontWeight>700</FontWeight>
</Style>
</Title>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="newChart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Year(Fields!OrderDate.Value)*100+Month(Fields!OrderDate.Value)</GroupExpression>
</GroupExpressions>
</Grouping>
<Sorting>
<SortBy>
<SortExpression>=Fields!OrderDate.Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Label>=Fields!OrderDate.Value</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Height>6.125in</Height>
<SeriesGroupings>
<SeriesGrouping>
<StaticSeries>
<StaticMember>
<Label>Cost</Label>
</StaticMember>
<StaticMember>
<Label>Sales</Label>
</StaticMember>
<StaticMember>
<Label>Target</Label>
</StaticMember>
</StaticSeries>
</SeriesGrouping>
</SeriesGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundGradientEndColor>White</BackgroundGradientEndColor>
<BackgroundGradientType>TopBottom</BackgroundGradientType>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title>
<Style />
</Title>
<Style />
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<MinorTickMarks>Outside</MinorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>6.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>da5964d0-11a7-4e51-9b22-cc4fa55fdd7a</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=(local);initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT [Order Details].UnitPrice, [Order
Details].Quantity, Orders.OrderDate
FROM Orders INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>bc811835-2302-4f9e-9c89-a99d4d3f5fd2</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>