Store the output of executesq

Store the output of executesq

• Manikandan MSFT

  >>The only problem is that the value is not stored in the variable @count

  You just didn't set the value for it. Try:

  set @sql = N'Select @countOUT = count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'

  
  

• Sajith

  Thaks! I just got the error message. So this would be an example of when to use managed code :))

  Imports System

  Imports System.Data

  Imports System.Data.SqlClient

  Imports System.Data.SqlTypes

  Imports Microsoft.SqlServer.Server

  Partial Public Class UserDefinedFunctions

  <SqlFunction(DataAccess:=DataAccessKind.Read)> _

  Public Shared Function UnitsAvailable(ByVal ID As SqlInt32) As Integer

  Using con As New SqlConnection("context connection=true")

  Dim cmdText As String = "Select count(*) as UnitCount from units where projectid=" & ID.ToString & " and sold=0 and displayunit=1"

  Dim cmd As New SqlCommand(cmdText, con)

  con.Open()

  Return CType(cmd.ExecuteScalar, Integer)

  End Using

  End Function

  End Class

  
  

• Alan1203

  One stop further.

  Here it works:

  declare @id int

  set @id = 195

  declare @sql nvarchar(100)

  declare @params nvarchar(500)

  declare @count nvarchar(10)

  set @sql = N'Select @countOUT = count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'

  set @params = N'@countOUT nvarchar(10) OUTPUT';

  exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;

  select @count

  Here it still produces no output

  ALTER function [dbo].[UnitsAvailable] (@id int)

  returns int

  as

  begin

  declare @sql nvarchar(100)

  declare @params nvarchar(500)

  declare @count nvarchar(10)

  set @sql = N'Select @countOUT = count(*) from units where projectid=' + convert(varchar,@id) + 'and sold=0 and displayunit=1'

  set @params = N'@countOUT nvarchar(10) OUTPUT';

  exec sp_executesql @sql, @params, @countOUT=@count OUTPUT;

  return @count

  end

  
  

• Kapil Singhal

  >>ALTER function [dbo].[UnitsAvailable] (@id int)

  You are not allowed to execute any procedures other than extended from within a function! It will never work.

  
  

• Lee Meyers

  I would also recommend you to print your @sql variable before exceution. You did't provide enough spaces between parameter inserted and the 'and' keyword followed.
  
• Store the output of executesq