Menu
Migrating asp.net code (VB.net) to use functions and subroutines as parameters. Using MS Server Management Studio to create said functions and subs. Would like to test the functions from within MS SMS before testing them via the web page. Here's an example. Say I have a function called "dbo.getNumber"
I'm trying to test it using the following:
USE [someDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO declare @value int; select @value = dbo.getNumber; print @value; go When I type F5 (to run the "query") it gives the following msg:
"The name "dbo.getNumber" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted."
The function dbo.getNumber was accepted just fine, btw. (It's counting records of a database that meet certain criteria.)
Hopefully you can infer from the non-working code what I am trying to do.
How can I print the value of a function (for testing purposes) from within SMS?
Correct solution as per James Johnson, below:
USE [someDB] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO declare @value int; select @value = dbo.getNumber(); print @value; go Note the parens for the function invocation. Note also: intellisense in SMS underlines dbo.getNumber() as if it were an error, but running the query with F5 works and outputs the right result.
811
The syntax for assigning a value to a SQL variable within a SELECT query is @ var_name := value , where var_name is the variable name and value is a value that you're retrieving. The variable may be used in subsequent queries wherever an expression is allowed, such as in a WHERE clause or in an INSERT statement.
The returned integer value from the Stored Procedure, you need to make use of an Integer variable and use along with the EXEC command while executing the Stored Procedure. Syntax: DECLARE @ReturnValue INT. EXEC @ReturnValue = < Store Procedure Name > < Parameters > Select @ReturnValue.
Variables in SQL procedures are defined by using the DECLARE statement. Values can be assigned to variables using the SET statement or the SELECT INTO statement or as a default value when the variable is declared. Literals, expressions, the result of a query, and special register values can be assigned to variables.
You need to call it like this:
select @value = dbo.getNumber() If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
