Passing Variables to SQL Stored Procedure from Powershell

i'm having bit of difficulty passing variable stored procedure powershell 2.0 ms sql server 2008. problem powershell script, stored procedure, or maybe (probably) both.

the purpose retrieve date entries in our staff scheduling software on per-location basis. that, write icalendar ics file each location out web server google calendar subscribes to. separate stored procedure each location, thought bit more elegant , manageable have 1 , pass location variable in invoke-cmdsql commandlet. i'd loop through array of locations , call commandlet each time.

here's powershell bit:

 

$ds = invoke-sqlcmd -serverinstance mydbserver -database my_db -query "exec dbo.spgetresults" -variable "@assignname=$location" 

 

i've tried without '@' in front of variable name well.

 

and here's have in stored procedure:

 

use [my_db] go /****** object: storedprocedure [dbo].[spgetresults] script date: 04/08/2011 12:24:23 ******/ set ansi_nulls on go set quoted_identifier on go --  alter procedure [dbo].[spgetresults] as begin 	-- set nocount on added prevent result sets from 	-- interfering select statements. 	set nocount on;   -- insert statements procedure here 	declare @assignname nvarchar, @startdate date, @enddate date; 	set @startdate = dateadd(day,-60,getdate()); 	set @enddate = dateadd(day,30,getdate()); 	 	if @assignname is not null 		select e.lastname as title, sd.scheddataid as 'uid', 			cast(sd.assigndate as varchar)+ 't' + right('00000'+cast((sd.starttime*100) as varchar),6) + 'z' as 'starttime', 			cast(sd.assigndate as varchar)+ 't' + right('00000'+cast((sd.endtime*100) as varchar),6) + 'z' as 'endtime' 		from scheddata as sd inner join  			employee as e on e.employeeid = sd.employeeid inner join 			shift as sh on sh.shiftid = sd.shiftid inner join 			assignment as on a.assignid = sh.assignid 		where convert(date, convert(char(8),sd.assigndate)) between @startdate and @enddate and a.assignname = @assignname; 	else 		select e.lastname as title, sd.scheddataid as 'uid', 			cast(sd.assigndate as varchar)+ 't' + right('00000'+cast((sd.starttime*100) as varchar),6) + 'z' as 'starttime', 			cast(sd.assigndate as varchar)+ 't' + right('00000'+cast((sd.endtime*100) as varchar),6) + 'z' as 'endtime' 		from scheddata as sd inner join  			employee as e on e.employeeid = sd.employeeid inner join 			shift as sh on sh.shiftid = sd.shiftid inner join 			assignment as on a.assignid = sh.assignid 		where convert(date, convert(char(8),sd.assigndate)) between @startdate and @enddate; end 

right now, it's returning results instead of filtering on @assignname variable, i'm assuming @assignname in fact null each time run script.

any appreciated!

 

sup pr0t0,

the syntax pass variable (using -variable switch) following:

  #method-1: think you're trying do  $query = "exec sp_who @loginame=`$(loginame)"  $db = "master"  $instance = "(local)"  $user = 'sa'    invoke-sqlcmd -query $query -database $db -serverinstance $instance -variable "loginame='${user}'" | ft  

i used "sp_who" keep things simple , generic. seem pretty savvy; no doubt can adapt method to. opt "ghetto" way (a.k.a., method #2) demonstrated following:

  #method-2: ghetto way a.k.a., way or easy way  $user = 'sa'  $query = "exec sp_who '${user}'"  $db = "master"  $instance = "(local)"    invoke-sqlcmd -query $query -database $db -serverinstance $instance | ft    

i interpolate byatch.

cheers,
adam

---

adam

Windows Server  >  Windows PowerShell