current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm using SQL Server 2008 R2 and have a linked SQL 2012 Express database. On the 2008 box I am creating a stored procedure to be used in a report. I am wanting to pass a date variable so the user can enter a to and from date in reporting services. However, I have been trying to get the openquery parameter code correct and it's just not happening. Here is my code:

@Custvar VARCHAR(1000),
@fromperiod datetime,
@toperiod datetime

@fromperiod varchar(8),
@toperiod varchar(8)


AS

SET NOCOUNT ON

DECLARE @sqlQuery VARCHAR(8000)
DECLARE @finalQuery VARCHAR(8000) 

DECLARE @fromperiodvar VARCHAR(8)
DECLARE @toperiodvar VARCHAR(8)

--SET @fromperiodvar=(CONVERT(CHAR(8), @fromperiod, 112)) 
--SET @toperiodvar=(CONVERT(CHAR(8), @toperiod, 112)) 


SELECT * FROM OPENQUERY
([ServerName\SQLEXPRESS],'
SELECT
    (DateDiff(DAY ,otrs.dbo.ticket.create_time , otrs.dbo.ticket.change_time) ) AS Open_to_close_days_duration ,
    otrs.dbo.ticket.create_time ,
    otrs.dbo.ticket.change_time,
    otrs.dbo.queue.name
FROM
    otrs.dbo.ticket
    INNER JOIN otrs.dbo.queue
        ON otrs.dbo.ticket.queue_id = otrs.dbo.queue.id
WHERE
    ((((DateDiff(DAY ,otrs.dbo.ticket.create_time ,otrs.dbo.ticket.change_time))) > 0)
        AND((otrs.dbo.ticket.ticket_state_id) = 3 OR(otrs.dbo.ticket.ticket_state_id) = 2 OR(otrs.dbo.ticket.ticket_state_id) = 10))
        AND otrs.dbo.ticket.create_time >= ''''+ @fromperiodvar + '''' and otrs.dbo.ticket.create_time <=''''+ @toperiodvar + ''''
ORDER BY
    otrs.dbo.ticket.queue_id;
')
GO

So where am I going wrong? I am unable to have SQL prompt me for the to and from dates?

Any help would be greatly appreciated.

Regards,

Michael

share|improve this question

1 Answer 1

up vote 1 down vote

For an ad hoc batch, you need to type the date values and assign them to the variables:

DECLARE @fromperiod varchar(8),
        @toperiod   varchar(8);

SELECT @fromperiod = '20130101',
       @toperiod = '20130131';

An ad hoc T-SQL batch can't prompt you; it's not interactive like VB in a Windows Form. You could do this by using the Debug button on the toolbar, and then editing the values in the Locals tab, but this seems to be a pretty cumbersome way to avoid a little typing:

enter image description here

There are probably also ways you could use templates or snippets to have a slightly easier way to input values, but I don't find the above that much of a blocker in the first place.

For Reporting Services, I know there are ways to allow users to enter data and have those passed at parameters, but someone else is going to have to give you the skinny on that (the documentation might be helpful as well). I can spell Reporting Services but not much more than that. If this is a stored procedure it probably makes things a lot easier.

share|improve this answer
    
Out of curiosity is there a reason to prefer SELECT over SET in your example? Coming from a development background SET makes more sense to me... –  Max Vernon Oct 25 '13 at 19:21
    
@Max only that you can't use SET to perform multiple assignments. I find lines and lines of SET @variable = cluttered. I also prefer SELECT @variable = column FROM dbo.table; over SET @variable = (SELECT column FROM dbo.table); for similar verbosity reasons, and like to be consistent. Unless I'm making a point that something is a single variable assignment, I use SELECT pretty consistently. –  Aaron Bertrand♦ Oct 25 '13 at 19:21
    
Cool. I figured that was it, but I wasn't 100%. –  Max Vernon Oct 25 '13 at 19:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.