Subsonic 3 using User.Identity.Name versus Environment.Username for Audit Fields

I had the same issue, and I had to tweak the SQLServer.ttinclude to get it working. Inside that file find the GetSPParams() method and change one line:

from

string[] restrictions = new string[4] { DatabaseName, null, spName, null };

to

string[] restrictions = new string[3] { null, null, spName };

.

BlackMael's answer has a helpful link that helped me figure out how to debug and step through the template code.

Now I'm not 100% sure yet that my change is a good one (there may be some adverse effects). I just haven't had a chance to test it thoroughly and need to read up some more on Restrictions as they pertain to the GetSchema() method. But for now, that solved my problem and I can successfully pass in my stored proc parameter.

Update: This may have something to do with the fact that my DB file is embedded in the VS solutin in App_Data. Perhaps this works better out of the box with a stand-alone SQL Server instance.

If you have OUTPUT parameters you can do

DECLARE @retval int   
DECLARE @sSQL nvarchar(500);
DECLARE @ParmDefinition nvarchar(500);

DECLARE @tablename nvarchar(50)  
SELECT @tablename = N'products'  

SELECT @sSQL = N'SELECT @retvalOUT = MAX(ID) FROM ' + @tablename;  
SET @ParmDefinition = N'@retvalOUT int OUTPUT';

EXEC sp_executesql @sSQL, @ParmDefinition, @retvalOUT=@retval OUTPUT;

SELECT @retval;

But if you don't, and can not modify the SP:

-- Assuming that your SP return 1 value
create table #temptable (ID int null)
insert into #temptable exec mysp 'Value1', 'Value2'
select * from #temptable

Not pretty, but works.