SELECT 1

[jimmYLepp]

Does anyone know how to use paramters in a select statement. I thought is was something like this


SELECT 'Drive %s has %d space free', @drive_letter, @space_free


but all i get back is


Drive %s has %d space free C 2000


what I want is


Drive C has 2000 space free



Thanks for your help

Later jimmY

 

[jhall156]

I assume that these two parameters are at the top of your stored procedure.

This should work
SELECT 'Drive ' + @drive_letter + ' has ' + @space_free + ' space free'

You should probably check for nulls since any null will make the entire expression null


JHall

 

[SQLBill]

You aren't using Microsoft SQL Server's Transact-SQL language. In MS SQL Server variables are identified by the @ symbol (@localvariable).

I suggest you post your question in the appropriate forum.

-SQLBill

 

[jimmYLepp]

Thank you for your responses, it seems that I confused some people either by not fully explaining myself or by only using snipits of code. What I am trying to do is in transact SQL, and it is being done to reduce the amount of typing needed to make proper system outputs. Let's say that you want to send out the same information about but only change cetain words within the statement it would be something like this


DECLARE @message varchar( 255 )
DECLARE @variable1 varchar( 255 )
DECLARE @variable2 varchar( 255 )

SELECT @variable1 = 'Betty'
SELECT @variable2 = 'chilie'

SELECT @message = 'Hi ' + @variable1 + ', do you like ' @variable2 + ' before 10:00AM'

SELECT @message

Of course the output would be

Hi Betty, do you like chilie before 10:00AM

where as I would like to do something like this

DECLARE @message varchar( 255 )
DECLARE @variable1 varchar( 255 )
DECLARE @variable2 varchar( 255 )

SELECT @variable1 = 'Betty'
SELECT @variable2 = 'chilie'

SELECT @message = 'Hi %ls, do you like %ls before 10:00AM',
@variable1, @variable2

SELECT @message

And the out put would still be

Hi Betty, do you like chilie before 10:00AM


I know this sounds odd, but think of it this way, you have a table of canned messages, when you come across a problem while processing sopmething you page out the same message. Also, you can leave all of you messages in one place without haveing to split and then re-concatinate the line.


Later jimmY

 

[jhall156]

I don't think T-SQL will do this.

JHall

 

[zhavic]

T-SQL can do this but with only strings ( %s ) and only by extended stored procedure 'xp_sprintf':

DECLARE @ret_string varchar (255)
DECLARE @drive_letter char(1)
DECLARE @space_free varchar(10)

SELECT @drive_letter = 'C',
@space_free = 1234

EXECUTE master.dbo.xp_sprintf @ret_string OUTPUT, 'Drive %s has %s space free', @drive_letter, @space_free
SELECT @ret_string


Zhavic

---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[jimmYLepp]

Thank you zhavic, that was just what I was looking for

Later jimmY