September 30, 2008 at 5:08 pm
[font="Tahoma"]I have the following SQL Server 2005 Express stored procedure:
USE [DA]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[ReadTS]
@pointnames varchar,
@starttime varchar,
@endtime varchar
AS
BEGIN
SET NOCOUNT ON;
SELECT @pointnames FROM TS WHERE tstamp > @starttime AND tstamp < @endtime
END
The problem is that it tries to return an integer, instead of the recordset I'd like it to return.
When I look at what details I can in the SMS, it seems to be trying to return an int.
How do I get it to not attempt to return an int, but return a recordset?[/font]
September 30, 2008 at 5:12 pm
SELECT [put the list of columns in here] FROM TS WHERE tstamp > @starttime AND tstamp < @endtime
You were selecting the third parameter of the proc, that's why you only had 1 int returned in the data set.
September 30, 2008 at 5:14 pm
@pointnames is a string with my list of fields in it
I need to select the recordset based on them.
How do I get the string parameter to expand out?
September 30, 2008 at 6:07 pm
You'd need to use dynamic sql for that, which is usually a no-no.
Why are your business requirements?
September 30, 2008 at 6:16 pm
This is actually for a small part of the app, which I have working now in Excel VBA, using a Recordset Open method with a constructed string. I was only trying to get a stored procedure to work, so I could create a more complicated one with similar parameters. The idea being to make things a little more efficient, but it's not an intense enough application that I have to do it that way.
I have a hard time imagining the need for me to create many stored procedures, without being able to feed them the fields I need queried.
With the long-term plan for this database and application, I'd have to create thousands of stored procedures to be able to meet the user and application needs.
September 30, 2008 at 8:02 pm
If you can make sure that the users cannot input bad stuff in the string (search sql injection for loads of articles on the subject). Then use dynamic sql in the procedure. Just make sure to validate the inputs to protect the server from injection attacks and hacks.
October 1, 2008 at 9:03 am
OK, I'll do some research, thanks.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply