CODEWITHSUNDEEP
sqlvbscript
Mike
Mike

Reputation: 3

Issue displaying sql function result into msgbox

I have a SQL function that accepts 1 input and returns a varchar result. This works fine when executed in SSMS but gives a runtime error type mismatch when I try to display from VBScript. I intend to use this variable to retrieve other data in another section of code. I'm just using the display to visualize what I'm receiving and to help me understand what I'm doing.

SQL Function:

ALTER FUNCTION [dbo].[ufnGetLongModelFromSerial] (@serial varchar(8)) returns varchar(30)

AS

BEGIN
  RETURN (SELECT LongModel
  FROM [dbo].[Compressor], [dbo].[Models]
  WHERE [Compressor].[Serial] = @serial AND [dbo].[Compressor].[Model] = [dbo].[Models].[Model])
END
GO

--print result of function for testing
DECLARE @serial varchar(8) = '18HE3712'
PRINT [dbo].[ufnGetLongModelFromSerial] (@serial)

VBScript:

Option Explicit

'see http://www.w3schools.com/ado/ado_datatypes.asp
Const adParamInput = 1
Const adParamOutput = 2
Const adParamInputOutput = 3
Const adParamReturnValue = 4

Const adVarChar = 200

Const sSourceServer = "myserver"
Const sSourceDB = "myDB"

Dim connstr
Dim cmd
Dim SP_Name
Dim serial
Dim longModel

SP_Name = "ufnGetLongModelFromSerial"
connstr = "Provider=SQLOLEDB;Data Source=" & sSourceServer & "; Initial Catalog=" & sSourceDB & ";User Id=myid; Password=mypassword;"

Set serial = CreateObject("ADODB.Parameter")
serial.Direction = adParamInput
serial.Name = "serial"
serial.Size = 30
serial.Type = adVarChar
serial.Value = "18HD0363"

'set up sql command object
Set cmd = CreateObject("ADODB.Command")
With cmd
  .ActiveConnection = connstr
  .CommandType = 4
  .CommandText = SP_Name
  .Parameters.Append serial
End With

'execute command object and set local variable longModel to equal result of select statement in stored procedure
longModel = cmd.Execute

Wscript.Echo longModel
'tidy up and remove objects
Set serial = Nothing
Set cmd = Nothing

Upvotes: 0

Views: 166

Answers (1)

Jirka Jr
Jirka Jr

Reputation: 41

As cmd.Execute returns Recordset object, you should write

WScript.Echo longModel(0)

See also here

VBScript - Retrieving a Scalar Value From a Stored Procedure on SQL Server 2008

Upvotes: 0

Related Questions