0

I have a datetime column name [dbo].[Bus Station] that I want to pass it in T-SQL script as a parameter @StationName

DECLARE @CurrentTime TIME(0) = '20:53:00',
    @StationName TIME(0), -- Datetime Column Name
    @BusTime TIME(0);

SET @StationName = '[dbo].[Bus Station]'

SET @BusTime = (SELECT TOP 1 @StationName 
                FROM dbo.R1_pvt 
                WHERE @StationName >= CONVERT (time(0), @CurrentTime))

SELECT DateDiff(mi, @CurrentTime, @BusTime) % 60    As Minutes

How can I do it? :(

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3872646
  • 13
  • 3
  • I think you may have some errors in your query. You're assigning a string to `@StationName`, yet it's defined as `TIME(0)`. Also, your query looks like you're trying to return a `Time`, yet you're selecting whatever field name would be passed in (in your example it was `@StationName`. – STLDev Aug 03 '17 at 03:28

1 Answers1

1

It appears that what you want to do is execute a dynamic SQL statement. This can be done, but you must be careful because depending on where the input comes from that's used to build the query, you could be susceptible to a SQL injection attack. On Microsoft's information page on this method, it says:

IMPORTANT: Before you call EXECUTE with a character string, validate the character string. Never execute a command constructed from user input that has not been validated.

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/execute-transact-sql

To execute a dynamic SQL statement, build your query in a string (varchar, typically) variable, and then execute it. That's done by either using the EXECUTE() command or by calling the sp_executesql system stored procedure.

-- variable to store the query
DECLARE @query varchar(500)

-- build the query
SET @query = 'SELECT TOP 1 ' + @StationName + ' FROM dbo.R1_pvt WHERE ' + @StationName + ' >= CONVERT(time(0), ''' + CAST(@CurrentTime as varchar) + ''' )'

-- execute the query
EXECUTE(@query);

In your case, you also want to return a value. This can be done and has been covered well elsewhere on this site. I suggest you take a look at this question on Stack Overflow:

Getting result of dynamic SQL into a variable for sql-server

STLDev
  • 5,950
  • 25
  • 36
  • Thank you so much dear, It really helped me :), – user3872646 Aug 04 '17 at 14:33
  • But, how can I use [DateDiff(mi, @CurrentTime, @BusTime)] with [EXECUTE(@query)] ? – user3872646 Aug 04 '17 at 14:35
  • Simply incorporate those into the query you're building. The simplest approach to this is to build your query as you would normally, and get it working. Once you have a working static query, convert it to a dynamic query. – STLDev Aug 04 '17 at 14:38