Sql-server – SQL Linked Server Passing Date Parameters

linked-serverparametersql server

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

Best Answer

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.

Related Question