SQLServer Declare New Variable Based On Another Variables Value
Oct 26, 2005
Hi, I'm a complete newbie to SQLServer and t-sql generally. What I want to do is create a new variable in a stored procedure based upon the value of another variable.
eg in the loop below I want to create 10 new variables, called @var0,@var1,@var2 ...@var9
declare @varname nvarchar(10)
declare @i integer
select @i=0
while @i<10
begin
set @varname = cast(('@var'+cast(@i as char)) as nvarchar(10))
set @i=@i+1
end
Does anyone know of a way to do this?
View 6 Replies
ADVERTISEMENT
May 6, 2008
Hello,I am working with the driver JDBC and I have a strange error that I can't find in Google for example.Caused by: com.seeburger.smarti.util.SmartiEJBException: nested exception is: com.seeburger.smarti.dao.DAOSysException: java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Must declare the variable '@P2GROUP'.I don't know what is this variable '@P2GROUP'.Does anybody know it ?Thanks
View 2 Replies
View Related
Oct 13, 2014
I know how to set a variable for the entire query, but how do I set one per row?
I have one query which is growing beyond my ability to understand/maintain it. I factor in the sale price of an item based on cost, our markup, shipping & channel fees. The problem is that each one of these has it's own variability. I would like to be able to store inline calculations as a variable such that I can call & add them. Example:
My code currently looks like this
CREATE TABLE #tempPrice (
product NVARCHAR(40)
,price DECIMAL(18,2)
)
insert into #tempPrice (product,price) values('prod1','18.00')
[Code] ....
I would like to be able to work with it this way:
SELECT
product
,price
,Cast(price * 1.1 as decimal(18,2)) as markup
,@markup + 5 as 'markup-and-shipping' -- <-- This factors in all of the previous calculations, plus something new.
FROM #tempPrice
I use Microsoft SQL 2008
View 7 Replies
View Related
Feb 15, 2005
Or, is the only way to use "declare" and "set" only ?
View 1 Replies
View Related
Sep 12, 2014
I have a select statement that works fine. Now I need to create a view with this statement and I have problems with the variables that I need to declare.
Is there a way to include the declare command in a view?
I can not use a stored procedure this time.
This is the statement:
DECLARE @FirstDate DATE,
@SecondDate DATE,
@ThirdDate DATE
SELECT @FirstDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones
---SELECT @SecondDate = MAX(fecha_valor) FROM MPR_Historico_Posiciones WHERE fecha_valor<> @FirstDate
SELECT @SecondDate = dateadd(week,-1,max(fecha_valor)) FROM MPR_Historico_Posiciones
[code]....
View 2 Replies
View Related
Jan 8, 2008
Hello there,
i have been asked about a thing that, i think, is not possible. But maybe i am wrong.
question:
Is it possible to have a Stored Procedure in that the declaration of the variables is dynamic?
This means, can i get the variable name and the type of it from a database to create
a dynamic stored procedure that changes itself by firing a trigger.
Thanks for all oppinions and answers.
everWantedLINUX
View 5 Replies
View Related
Apr 3, 2007
I have created the following stored procedure in sql server 2005.I m using asp.net with C# 2005.
Can someone please rectify my errors why i m getting such type of the errors...
ALTER PROCEDURE CompanyStoredProcedure
@uspcompanyid INT NULL,
@uspcompanyname VARCHAR(20),
@uspaddress1 VARCHAR(30),
@frmErrorMessage AS VARCHAR(256) OUTPUT,
@RETURNVALUE AS INT OUTPUT,
@RETURNID AS INT OUTPUT
AS
BEGIN
SET NOCOUNT ON
/*
RETURN_VALUE Comments
1Data Inserted
2Data Updated
-9Other errors
*/
DECLARE @companyid INT,
@companyname VARCHAR(20),
@address1 VARCHAR(30)
--validation...
IF ( @uspcompanyname IS NULL OR @uspcompanyname = '' )
BEGIN
SET @RETURNVALUE = -9
SET @frmErrorMessage = 'Company Name is empty'
RETURN -9
END
IF EXISTS ( SELECT * FROM Companymaster WHERE Companyid = @companyid )
BEGIN
UPDATE Company
SET companyname = @companyname,
address1 = @address1
WHERE companyid= @uspcompanyid
SET @frmErrorMessage = 'Company Name/Address has been updated'
SET @RETURNVALUE = 2
END
ELSE
BEGIN
INSERT INTO companymaster ( companyname, address1 )
VALUES (@companyname,@address1)
SET @frmErrorMessage = 'Company Name/Address info has been Inserted'
SET @RETURNVALUE = 1
END
SET NOCOUNT OFF
END
THANXS in advance.
View 3 Replies
View Related
Sep 12, 2005
Hello everyone,
View 11 Replies
View Related
Jan 11, 2008
Declare @DBName varchar(25)
select @DBName = 'Production'
Select @DBName = @DBName + '.dbo.'+'sysfiles'
select @DBName
select * from @DBName
When I executes above lines of code in query analyser it give me an error like :
Server: Msg 137, Level 15, State 2, Line 5
Must declare the variable '@DBName'.
give me solution as soon as possible
Thanks
Aric
View 6 Replies
View Related
Oct 10, 2006
In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.
The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.
The MemberId field is a varchar(20) in the table.
Create procedure sp_GetClaims_BY_MemberID
@Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @memberid
EXEC sp_GetClaims_BY_MemberID '99999999999'
The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?
I am currently working in SQL 2000.
View 3 Replies
View Related
Sep 18, 2006
Hi,
I am declaring the cursor based on a query which is generated dynamically. but it is not working
Declare @tempSQL varchar(1000)
--- This query will be generated based on my other conditon and will be stored in a variable
set @tempsql = 'select * from orders'
declare cursor test for @tempsql
open test
This code is not working.
please suggest
Nitin
View 12 Replies
View Related
Jul 17, 2006
Hello!
I have a aspx page in which I have a Gidview populated by a sqlDataSouce.
This is my code:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="CostEmployee1.aspx.vb" Inherits="RecursosHumanos_CostEmployee1" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Untitled Page</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" CellPadding="4"
DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" Style="z-index: 100; left: 0px; position: absolute; top: 0px"> <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <Columns> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Editar" Text="Editar" runat="server" CommandName="Edit"></asp:LinkButton> </ItemTemplate> <EditItemTemplate> <asp:LinkButton ID="LinkButton1" runat="server" CausesValidation="True" CommandName="Update"
Text="Actualizar" style="color: white"></asp:LinkButton> <asp:LinkButton ID="LinkButton2" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancelar" style="color: white"></asp:LinkButton> </EditItemTemplate> </asp:TemplateField> <asp:TemplateField> <ItemTemplate> <asp:LinkButton ID="Apagar" Text="Apagar" runat="server" CommandName="Delete" OnClientClick='return confirm("Tem a certeza que deseja apagar este registo?");' CausesValidation="false"></asp:LinkButton> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_CostEmployee" InsertVisible="False" SortExpression="Id_CostEmployee"> <EditItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Eval("Id_CostEmployee") %>'></asp:Label> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label1" runat="server" Text='<%# Bind("Id_CostEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Id_Employee" SortExpression="Id_Employee"> <EditItemTemplate> <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label2" runat="server" Text='<%# Bind("Id_Employee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="FullName" SortExpression="FullName"> <EditItemTemplate> <asp:TextBox ID="textbox5" runat="server" Text='<%# Bind("FullName")%>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label6" runat="server" Text='<%# Bind("FullName") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="NumEmployee" SortExpression="NumEmployee"> <EditItemTemplate> <asp:TextBox ID="TextBox6" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label7" runat="server" Text='<%# Bind("NumEmployee") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Period" SortExpression="Period"> <EditItemTemplate> <asp:TextBox ID="TextBox2" runat="server" Text='<%# Bind("Period") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label3" runat="server" Text='<%# Bind("Period") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="CostHour" SortExpression="CostHour"> <EditItemTemplate> <asp:TextBox ID="TextBox3" runat="server" Text='<%# Bind("CostHour") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label4" runat="server" Text='<%# Bind("CostHour") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> <asp:TemplateField HeaderText="Date" SortExpression="Date"> <EditItemTemplate> <asp:TextBox ID="TextBox4" runat="server" Text='<%# Bind("Date") %>'></asp:TextBox> </EditItemTemplate> <ItemTemplate> <asp:Label ID="Label5" runat="server" Text='<%# Bind("Date") %>'></asp:Label> </ItemTemplate> </asp:TemplateField> </Columns> <RowStyle BackColor="#EFF3FB" /> <EditRowStyle BackColor="#2461BF" /> <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" /> <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" /> <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" /> <AlternatingRowStyle BackColor="White" /> </asp:GridView> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:EuroscutConnectionString %>" SelectCommand="SELECT [HR.CostEmployee].Id_CostEmployee, [HR.CostEmployee].Id_Employee, [HR.CostEmployee].Period, [HR.CostEmployee].CostHour, [HR.CostEmployee].Date, [HR.Employee].FullName, [HR.Employee].NumEmployee FROM [HR.CostEmployee] INNER JOIN [HR.Employee] ON [HR.CostEmployee].Id_Employee = [HR.Employee].Id_Employee"
UpdateCommand="UPDATE [HR.CostEmployee] set Period = @Period, CostHour = @CostHour where Id_CostEmployee = @Id_CostEmployee"
DeleteCommand="DELETE from [HR.CostEmployee] where (Id_CostEmployee = @Id_CostEmployee)"> <UpdateParameters> <asp:Parameter Name="Period" /> <asp:Parameter Name="CostHour" /> <asp:Parameter Name="Id_CostEmployee" /> </UpdateParameters> <DeleteParameters> <asp:Parameter Name="Id_CostEmployee" Type="int32" /> </DeleteParameters> </asp:SqlDataSource> </div> </form></body></html> When I run the page I'm able to edit the row but when I try to delete it gives me the error:
Must declare the scalar variable "@Id_CostEmployee".
I'm tired of "googling" this error, and I've tried all the advices, nothing...
I don't know what is happening here, I have 5 other forms, all simillar and they all work!
Any suggestions, pleeeeaaaase?
Thank's!
Paula
View 10 Replies
View Related
Feb 20, 2007
I'm making an ecommerce web app from following the Apress "Beginning ASP.Net 2 E-commerce with C#" book, and when I implement a stored procedure (I made a mdf DB in the app_Data folder), I get the following message: Must declare the scalar variable @CategoryIDThe code used to obtain this error is below: CREATE PROCEDURE DeleteCategory(@CategoryINT int)ASDELETE FROM CategoryWHERE CategoryID = @CategoryID I get this error with every Stored Procedure I try to implement. What should I do to fix this? In SQL Server 2k5 Management Studio, this problem does not present itself.
View 1 Replies
View Related
Mar 30, 2007
Hi with the code below I am getting the error
Error inserting record. Must declare the scalar variable "@contractWHERE"
I removed @contract and it then gave me the error
Error inserting record. Must declare the scalar variable "@zipWHERE"
I was wondering if some can point me in the right direction for fixxing this
protected void cmdUpDate_Click(Object sender, EventArgs e)
{
//Define ADO.NET Objects.
string updateSQL;
updateSQL = "UPDATE Authors SET ";
updateSQL += "au_id=@au_id, au_fname=@au_fname, au_lname=@au_lname, ";
updateSQL += "phone=@phone, address=@address, city=@city,state=@state, ";
updateSQL += "zip=@zip, contract=@contract";
updateSQL += "WHERE au_id@au_id_original";
SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(updateSQL, con);
//Add the parameters.
cmd.Parameters.AddWithValue("@au_id", txtID.Text);
cmd.Parameters.AddWithValue("@au_fname", txtFirstName.Text);
cmd.Parameters.AddWithValue("@au_lname", txtLastName.Text);
cmd.Parameters.AddWithValue("@phone", txtPhone.Text);
cmd.Parameters.AddWithValue("@address", txtAddress.Text);
cmd.Parameters.AddWithValue("@city", txtCity.Text);
cmd.Parameters.AddWithValue("@state", txtState.Text);
cmd.Parameters.AddWithValue("@zip", txtZip.Text);
cmd.Parameters.AddWithValue("@contract", Convert.ToInt16(chkContract.Checked));
cmd.Parameters.AddWithValue("au_id_original", lstAuthor.SelectedItem.Value);
//Try to open the database and execute the update
try
{
con.Open();
int updated = cmd.ExecuteNonQuery();
lblStatus.Text = updated.ToString() + " records inserted.";
}
catch (Exception err)
{
lblStatus.Text = "Error inserting record. ";
lblStatus.Text += err.Message;
}
finally
{
con.Close();
}
}
}
Many Thanks in advance
View 3 Replies
View Related
Jun 17, 2013
CREATE PROCEDURE [dbo].[Testing]
@FilteredID VARCHAR (MAX),
@SchoolCode VARCHAR (MAX),
[Code]....
I tried to execute above sproc in SQL Server Management Studio , I received the error: Must declare the scalar variable "@Score1".
View 5 Replies
View Related
Aug 31, 2006
OK i have my stored procedure all set up and working.. But when i try and add a second variable called @iuser and then after i execute the stored procedure, i get an error saying:-
ERROR
"Must declare scalar variable @iuser"
Here is the code i am using in my stored proc, also my stored proc worked fine before i used a second variable??!
//BEGIN
ALTER PROCEDURE [dbo].[putpending]
(@cuuser nvarchar(1000), @iuser nvarchar(1000))
AS
Declare @sql nvarchar(1000)
SELECT @sql =
'INSERT INTO ' +
@cuuser +
' (Pending) VALUES (@iuser)'
EXECUTE (@sql)
RETURN
//END
And i know my VB.NET code is working but i will put it in anyway:-
//BEGIN
'variables
Dim user As String
user = Profile.UserName
Dim intenduser As String
intenduser = DetailsView1.Rows(0).Cells(1).Text.ToString()
'connection settings
Dim cs As String
cs = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|friends.mdf;Integrated Security=True;User Instance=True"
Dim scn As New SqlConnection(cs)
'parameters
Dim cmd As New SqlCommand("putpending", scn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@cuuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@cuuser").Value = user
cmd.Parameters.Add("@iuser", SqlDbType.NVarChar, 1000)
cmd.Parameters("@iuser").Value = intenduser
'execute
scn.Open()
cmd.ExecuteNonQuery()
scn.Close()
//END
Any ideas why i am getting this error message?
View 10 Replies
View Related
Mar 24, 2007
the following is my code
can anybody rectify my problem that i get when running my application
"Must declare scalar variable @ID"
<asp:GridView ID="GridView1" DataKeyNames="ID" runat="server" AutoGenerateColumns="False" BackImageUrl="~/App_Themes/SkinFile/back1.jpg"
BorderColor="Teal" BorderStyle="Solid" DataSourceID="SqlDataSource1">
<Columns>
<asp:CommandField ButtonType="Button" ShowSelectButton="True" />
<asp:BoundField DataField="ID" HeaderText="ID" SortExpression="ID" />
<asp:BoundField DataField="Answers" HeaderText="Answers" SortExpression="Answers" />
</Columns>
</asp:GridView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:YahooConnectionString7 %>"
DeleteCommand="DELETE FROM Answers WHERE (ID = @ID)" InsertCommand="INSERT INTO Answers(ID, Answers) VALUES (@ID, @Answers)"
SelectCommand="SELECT Answers.* FROM Answers" UpdateCommand="UPDATE Answers SET ID = @ID, Answers = @Answers WHERE (ID = @ID)">
<DeleteParameters>
<asp:Parameter Name="ID" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="ID" />
<asp:Parameter Name="Answers" />
</InsertParameters>
</asp:SqlDataSource>
View 1 Replies
View Related
Dec 10, 2007
Hello,
I have the following SP, which gives out a "Error 137: Must declare variable @tmp_return_tbl" error.
This is the important part of the code:
.
.
.
-- DECLARE TABLE VARIABLE
DECLARE @tmp_return_tbl TABLE (tID int, Text_Title nvarchar(30), Text_Body nvarchar(100))
-- fill out table variable USING A SELECT FROM ANOTHER TABLE VARIABLE
-- NO PROBLEM HERE
INSERT INTO @tmp_return_tbl
SELECT TOP 1 * FROM @tmp_tbl
ORDER BY NEWID()
-- TRYING TO UPDATE A TABLE
UPDATE xTable
SET xTable.fieldY = xTable.fieldY + 1
WHERE xTable.tID = @tmp_return_tbl.tID --THIS PRODUCES THE ERROR
.
.
.
I know I cannot use a table variable in a JOIN without using an Alias, or use it directly in dynamic SQL (different scope) - but is this the problem here? What am I doing wrong?
Your help is much appreciated.
View 3 Replies
View Related
Dec 30, 2007
I am not sure why the following does not work...
I am declaring a variable to hold a string to be used in my script. The contents of the variable looks perfect and works independently, the variable it does not work. (BTW, do you call this a variable in T-SQL too?). The idea is to pass the value to the script to generate different results sets.
//Parameter
DECLARE @Codes varchar(8000);
SET @Codes = '''07-1110_CHA,1'',''07-1110_DCV,2''';
//Examine contents - results: '07-1110_CHA,1','07-1110_DCV,2'
Select @Codes;
//Works fine
Select screening_cd,check_amount
From accounting
Where screening_cd in ('07-1110_CHA,1','07-1110_DCV,2');
//This does not work
Select screening_cd,check_amount
From accounting
Where screening_cd in (@Codes);
TIA
View 4 Replies
View Related
Jan 14, 2008
Can anyone tell me why I keep getting this error? I am declaring the variable, but it's not recognizing it? What am I missing?
------error---------------
Server: Msg 137, Level 15, State 2, Procedure sp_CopyData, Line 85
Must declare the variable '@DatabaseFrom'.
-----sp----
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create Procedure dbo.sp_CopyData
(@ClientAbbrev nchar(4) )
AS
DECLARE @DatabaseFrom varchar(100)
Set @DatabaseFrom = @ClientAbbrev + '.dbo.tsn_ClaimStatus'
--------------------------------------------------------------
delete from sherrisplayground.dbo.tsn_ClaimStatus
where csclientcode = @ClientAbbrev
---Insert Data from Original table into copied table---------
Insert into [AO3AO3].sherrisplayground.dbo.tsn_ClaimStatus (
CsClientCode,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName)
select
@ClientAbbrev,
ClaimStatusID,
Pat,
Claim,
[ID],
Code,
[Date],
ActionID,
Comment2,
Comment3,
Comment4,
[Followup Date],
Checkamt,
UserName
from @DatabaseFrom
return
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 2 Replies
View Related
Mar 24, 2008
Following stored proc uses dynamic sql but it gives the error
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@ProjectBenefitID".
though its declared .please. tell the workaround
ALTER PROCEDURE [dbo].[spPMPT_GetBenefit]
@ProjectBenefitID INT,
@OrderBYVARCHAR(40),
-- Parmeters for Paging [Start]
@TotalPages INT OUT ,
@CurrentPageNumber INT OUT ,
@NumberOfRecordsINT = 5, /*PageSize*/
@CurrentPage INT = 0/*PageNumber*/
-- Parmeters for Paging [End]
AS
SET NOCOUNT ON
DECLARE @TMPFLOAT
DECLARE @ErrorMsgID INT
DECLARE@ErrorMsg VARCHAR(200)
----- Paging declarations start
DECLARE @SQLFinal NVARCHAR(4000)
DECLARE @Count INT
DECLARE @SC VARCHAR(4000)
----- Paging declarations end
DECLARE@SelectASVARCHAR(4000)
DECLARE@FromASVARCHAR(4000)
DECLARE@WhereASVARCHAR(4000)
DECLARE@LsOrderBy ASVARCHAR(4000)
-- Initialize vars
SET @SC= ''
SET @From= ''
SET @Where= ''
SET @Select= ''
SET @SQLFinal= ''
SET @Count= 0
IF (@CurrentPage = 0 OR @CurrentPage IS NULL)
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Page Number cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@NumberOfRecords = 0 OR @NumberOfRecords IS NULL )
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. Number of records per page cannot be zero.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
IF (@Orderby IS NULL OR @Orderby = '')
BEGIN
--Generate error message
SELECT @ErrorMsg = 'Error occured in Stored Procedure ' + (SELECT name from sysobjects WHERE ID = @@PROCID) + '. The Order by cannot be null.'
--Raise error to the user
RAISERROR (@ErrorMsg,16/*severity*/,1/*state*/)
--Return error indicator
RETURN (-1)
END
CREATE TABLE #TEMP_BENEFIT1
(
AssessBenefitID INT,
ProjectBenefitID INT,
ExpectedQuantity INT,
ExpectedQuality VARCHAR(2000),
Comments VARCHAR(2000)
)
INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID,
Quantity,Quality,
Comments
FROM PMPT_AssessBenefit
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationID
CREATE TABLE #TEMP_BENEFIT2
(
AssessBenefitID INT,
ProjectBenefitID INT,
ActualQuantity INT,
QtyFileID INT,
QtyFileName VARCHAR(100),
QtyFilepath VARCHAR(100),
ActualQuality VARCHAR(2000),
QuaFileID INT,
QualFileName VARCHAR(100),
QualFilepath VARCHAR(100),
Comments VARCHAR(2000),
refAssessBenefitID INT,
DateasON DATETIME
)
INSERT INTO #TEMP_BENEFIT2 SELECT PAB.AssessBenefitID,PAB.ProjectBenefitID,
PAB.Quantity,pab.qtyFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.qtyFileID) as QtyFilepath,
PAB.Quality,pab.quaFileID,
(SELECT FileName FROM PMPT_Files WHERE FileID = pab.quaFileID) AS QualFileName,
(SELECT UploadedFilePath FROM PMPT_Files WHERE FileID = pab.quaFileID) as QuaFilepath,
PAB.Comments,PAB.refEXPAssessBenefitID,PAB.DateasON
FROM PMPT_AssessBenefit PAB
WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='A'
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID=@ProjectBenefitID)
IF @UNIT IS NULL
SET @UNIT = ''
SET @Select='
DECLARE @UNIT VARCHAR(100)
SELECT @UNIT=NAME FROM PMPT_Picklists WHERE PicklistID = (SELECT unitID FROM PMPT_ProjectBenefits WHERE ProjectBenefitID='+CONVERT(VARCHAR(10),@ProjectBenefitID))+'
SELECTT1.AssessBenefitID, CAST(T1.ExpectedQuantity AS VARCHAR)+'' ''+ @UNIT as ExpectedQuantity,
CAST( T2.ActualQuantity AS VARCHAR)+'' ''+ @UNIT as ActualQuantity, T2.QtyFileID, T2.QtyFileName AS QtyFileName ,T2.QtyFilepath, T1.ExpectedQuality AS ExpectedQuality , T2.ActualQuality AS ActualQuality ,
T2.QuaFileID,T2.QualFileName AS QualFileName ,T2.QualFilepath, T2.COMMENTS,CONVERT(VARCHAR(10),T2.DateasON,103) AS DateasON
FROM#TEMP_BENEFIT1 T1,#TEMP_BENEFIT2 T2
WHERET1.AssessBenefitID = T2.refAssessBenefitID'
View 1 Replies
View Related
Jul 23, 2005
I'm attempting to modify some Crosstab generating code, and I need someadvice/examples.Currently, the code uses a single string variable to store thedynamically generated query (www.johnmacintyre.ca). The problem is thatI am trying to pivot biological taxonomy information, and may end upwith a table containing over 200 columns. This takes the dynamic stringwell over the 8000char limit for variables.[color=blue]>From my understanding, the EXEC() command does not have the 8000char[/color]limit if the execution string is broken into chunks, and concatenatede.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need isto:1) start a counter at the beginining of the dynamic generation2) append the counter value to the end of a string variable name3) DECLARE the new variable and attach that loop cycle of text to it,or attach each chunk of characters < 80004) build the EXEC() string by concatenating each dynamic varibleCan this be done? Should it be done? Is there a better way to addressthis type of problem?Thanks for any ideas or insightsTim Pascoe
View 5 Replies
View Related
Feb 29, 2008
create PROCEDURE [Update_Purged]
AS
Declare @Stg_Purged_union table
(PurgedAccountUnionID int IDENTITY,
Coid char(50),
FacilityId int,
AccountID int,
PatientName char(50),
PatientNo char(50),
PT char(50),
ST char(50),
PurgeDt datetime);
BEGIN
INSERT INTO @Stg_Purged_Union
([Coid]
,null
,null
,[PatientName]
,[PatientNo]
,[PT]
,
,[PurgeDt])
select coid, patientname, patientno, pt, st, purgedt from
[Stg_PurgedAccount]
--updating the facilityid in @stg_purged_account_file_union
update @stg_purged_union set facilityid=(select
facilityid from appfacility as b
where b.unitnum=(select
unitnum from appfacility as c
where c.unitnum=b.unitnum) and @Stg_Purged_Union.coid=b.unitnum)
I am getting the following error.
Must declare the scalar variable "@Stg_Purged_Union"
View 4 Replies
View Related
Jul 25, 2006
ive encounter this problem when i install the application at client side..enfect, i try it on all the PC at my office, i wouldnt encounter this problem..can anyone tell me what is possibly wrong with this?here is the code possible code effectedASPDim myDataset As Data.DataSet = New Data.DataSet Dim conn As Connection = New Connection("AbnormalControlDataConnString", "P_CreateAbnormalFormSect0") conn.AddParameter("Pending", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80) conn.AddParameter("CreateBy", Me.Session.Item("UserId"), Data.SqlDbType.Char, 80) conn.executeSproc(myDataset) Me.Session.Add("RefNo", myDataset.Tables(0).Rows(0).Item(0)) conn = Nothing myDataset = Nothing gridview1.dataBind() <~~~~~~ i have this gridview bind another sproc named[P_GetAbnormalFormSect0)conn.vb Public Sub New(ByVal con As String, ByVal sprocName As String) configCon = con connectionString = System.Configuration.ConfigurationManager.ConnectionStrings(configCon).ConnectionString ' myConnection = New System.Data.SqlClient.SqlConnection(connectionString) myCommand = New System.Data.SqlClient.SqlCommand() myCommand.Connection = myConnection myCommand.CommandType = CommandType.StoredProcedure myCommand.CommandText = sprocName End Sub Public Sub AddParameter(ByVal name As String, ByVal value As String, ByVal type As SqlDbType, ByVal size As Int16) myCommand.Parameters.Add(name, type, size) myCommand.Parameters.Item(myCommand.Parameters.Count - 1).SqlValue = value End Sub Public Sub executeSproc(ByRef dataset As Data.DataSet) myConnection.Open() myDataSet = New System.Data.DataSet() myDataSet.CaseSensitive = True dataAdapter = New System.Data.SqlClient.SqlDataAdapter() dataAdapter.SelectCommand = myCommand If Not dataset Is Nothing Then dataAdapter.TableMappings.Add("asd", "UserInfo") dataAdapter.Fill(myDataSet) dataset = myDataSet myConnection.Close() Else myCommand.ExecuteNonQuery() End If End SubSprocset ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_GetAbnormalFormSect0] -- Add the parameters for the stored procedure here @REFNO CHAR(11) = NULLASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; DECLARE @ErrorCode int SET @ErrorCode = 0 SELECT * FROM dbo.AFStatusInfo WHERE dbo.AFStatusInfo.RefNo = @REFNO IF(@@ROWCOUNT = 0) BEGIN SET @ErrorCode = -1 END IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = @@ERROR END RETURN @ErrorCodeEND-----------------------------------------------------------------------------------------------set ANSI_NULLS ONset QUOTED_IDENTIFIER ONGOALTER PROCEDURE [dbo].[P_CreateAbnormalFormSect0] -- Parameters for the stored procedure @Pending CHAR(80), @CreateBy CHAR(80) ASBEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- @ErrorCode : 0 No Error -- 1 No Data (Ignore when no ResultSet required) -- 2 Parameter not complete -- 3 Conflict with existing data -- Other error code please refer to @@ERROR code DECLARE @ErrorCode int SET @ErrorCode = 0 DECLARE @Id VARCHAR(11) DECLARE @Temp VARCHAR(4) SET @Temp = CAST((SELECT COUNT(*) FROM [AFStatusInfo] WHERE RefNo like ('BS200607%'))AS CHAR(3)) SET @Temp = @Temp + 1000 SET @Id = 'BS' SET @Id = @Id + CONVERT(CHAR(4),GETDATE(),20) SET @Id = @Id + CONVERT(CHAR(2),GETDATE(),10) SET @Id = @Id + SUBSTRING(@Temp,2,3) INSERT INTO [AFStatusInfo] (RefNo ,FlowStatus ,[Pending] ,[CreateBy] ,[DateCreated]) VALUES (@Id ,'New' ,@Pending ,@CreateBy ,CONVERT(CHAR(8), GETDATE(), 3)) SELECT @Id AS "RefNo" IF( @@ERROR <> 0 ) BEGIN SET @ErrorCode = @@ERROR END RETURN @ErrorCode OnError: RETURN @ErrorCodeEND----------------------------------------------------------------------------------------------------------------
View 1 Replies
View Related
May 3, 2007
I’m having trouble with a datalist. The default view is the Item Template which has an Edit button. When I click the Edit button, I run the following code (for the EditCommand of the Datalist):
DataList1.EditItemIndex = e.Item.ItemIndex
DataBind()
It errors out with the message “Must declare variable @ID�.
I’ve used this process on other pages without problem.
The primary key for the recordsource that populates this datalist is a field named “AutoID�. There is another field named ID that ties these records to a master table. The list of rows returned in the datalist is based off the ID field matching a value in a dropdown list on the page (outside of the datalist). So my SQLdatasource has a parameter to match the ID field to @ID. For some reason, it's not finding it and I cannot determine why. I haven't had this issue on other pages.
Here’s my markup of the SQLDataSource and the Datalist/Edit Template:
<asp:SqlDataSource ID="SqlDataSource4" runat="server" ConnectionString="<%$ ConnectionStrings:SMARTConnectionString %>"
DeleteCommand="DELETE FROM [tblSalesSupport] WHERE [NBID] = @NBID"
InsertCommand="INSERT INTO [tblSalesSupport] ([ID], [NBNC], [NBEC], [Description], [Estimate], [CompanyID], [CompanyName], [ProjectNumber]) VALUES (@ID, @NBNC, @NBEC, @Description, @Estimate, @CompanyID, @CompanyName, @ProjectNumber)"
SelectCommand="SELECT * FROM [tblSalesSupport] WHERE ([ID] = @ID)"
UpdateCommand="UPDATE [tblSalesSupport] SET [ID] = @ID, [NBNC] = @NBNC, [NBEC] = @NBEC, [Description] = @Description, [Estimate] = @Estimate, [CompanyID] = @CompanyID, [CompanyName] = @CompanyName, [ProjectNumber] = @ProjectNumber WHERE [NBID] = @NBID">
<DeleteParameters>
<asp:Parameter Name="NBID" Type="Int32" />
</DeleteParameters>
<UpdateParameters>
<asp:Parameter Name="ID" Type="Int32" />
<asp:Parameter Name="NBNC" Type="Boolean" />
<asp:Parameter Name="NBEC" Type="Boolean" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Estimate" Type="Decimal" />
<asp:Parameter Name="CompanyID" Type="Int32" />
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="ProjectNumber" Type="String" />
<asp:Parameter Name="NBID" Type="Int32" />
</UpdateParameters>
<SelectParameters>
<asp:ControlParameter ControlID="ddlFind" Name="ID" PropertyName="SelectedValue" Type="Int32" />
</SelectParameters>
<InsertParameters>
<asp:Parameter Name="ID" Type="Int32" />
<asp:Parameter Name="NBNC" Type="Boolean" />
<asp:Parameter Name="NBEC" Type="Boolean" />
<asp:Parameter Name="Description" Type="String" />
<asp:Parameter Name="Estimate" Type="Decimal" />
<asp:Parameter Name="CompanyID" Type="Int32" />
<asp:Parameter Name="CompanyName" Type="String" />
<asp:Parameter Name="ProjectNumber" Type="String" />
</InsertParameters>
</asp:SqlDataSource>
<asp:DataList CssClass="MainFormDisplay" ID="DataList1" runat="server" DataKeyField="NBID" DataSourceID="SqlDataSource1" width="100%">
<HeaderTemplate>….</HeaderTemplate>
<ItemTemplate>….</ItemTemplate>
<EditItemTemplate>
<table border="0" style="width: 100%">
<tr class="MainFormDisplay" valign="top">
<td colspan="8">
<asp:TextBox ID="txtNBID" runat="server" Text='<%# Eval("NBID") %>' Visible="true"></asp:TextBox>
<asp:TextBox ID="txtID" runat="server" Text='<%# Bind("ID") %>' Visible="True"></asp:TextBox></td>
</tr>
<tr class="MainFormDisplay">
<td valign="top" style="width: 100px"><asp:Checkbox ID="chkNBNC" runat="server" Checked='<%# Bind("NBNC") %>' /></td>
<td style="width: 100"><asp:CheckBox ID="chkNBEC" runat="server" Checked='<%# Bind("NBEC") %>' Width="100px" /></td>
<td style="width: 100px"><asp:TextBox ID="txtCompanyName" runat="server" Text='<%# Bind("CompanyName")%>' Width="100px"></asp:TextBox></td>
<td style="width: 100px"><asp:TextBox ID="txtProjectNumber" runat="server" Text='<%# Bind("ProjectNumber") %>' Width="100px"></asp:TextBox></td>
<td style="width: 100px"><asp:TextBox ID="txtDescription" runat="server" Text='<%# Bind("Description") %>' Width="100px"></asp:TextBox></td>
<td style="width: 100px"><asp:TextBox ID="txtEstimate" runat="server" Text='<%# Bind("Estimate","{0:N2}") %>' Width="100px"></asp:TextBox></td>
<td style="width: 55px"><asp:CheckBox ID="ckDeleteFlag" runat="server" /></td>
<td style="width: 100px"><asp:Button ID="ItemSaveButton" runat="server" CommandName="Update" Text="Save" />
<asp:Button ID="ItemCancelButton" runat="server" CommandName="Cancel" Text="Cancel" /></td>
</tr>
</table>
</EditItemTemplate>
</asp:DataList><br />
View 2 Replies
View Related
Aug 21, 2007
Hi,Can somone please tell me why I get the error 'Must declare the scalar variable "@recCount".' when I execute this Store Procedure? ALTER PROCEDURE dbo.CountTEst AS SET NOCOUNT ON DECLARE @sql as nvarchar(100); DECLARE @recCount as int SET @SQL = 'SELECT @recCount = COUNT(*) FROM Pool' exec sp_executesql @SQL RETURN @recCount In this case I expect RETURN value = 4.I've tried various approaches, including adding an OUTPUT parameter to the exec statement, but just can't get the syntax correct. Thanks
View 14 Replies
View Related
Sep 27, 2007
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection
Dim cntec As New SqlClient.SqlConnection(cnStrtec)
Dim datec As New SqlClient.SqlDataAdapter
Dim dstec As New DataSet
Dim cmdtec As New SqlClient.SqlCommand
so do I decalre above variable in module.vb as Public variable or is there any other way to do this.
Please some one give me idea how to do this
thank you
maxmax
View 3 Replies
View Related
Sep 27, 2007
In my asp.net application , I have to open/close SQL databse connection many time, and I dont want to declare Following variable everytime I open SQL database connection
Dim cntec As New SqlClient.SqlConnection(cnStrtec)
Dim datec As New SqlClient.SqlDataAdapter
Dim dstec As New DataSet
Dim cmdtec As New SqlClient.SqlCommand
so do I decalre above variable in module.vb as Public variable or is there any other way to do this.
Please some one give me idea how to do this
Please give me some examples
thank you
maxmax
View 1 Replies
View Related
Feb 22, 2008
I am not seeing the problem with this. I have a button at the bottom of this form that does this...
protected void Button1_Click(object sender, EventArgs e)
{
SqlDataSource1.Insert();
}
And the SqlDataSource code...<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:calldbConnectionString2 %>"
ProviderName="<%$ ConnectionStrings:calldbConnectionString2.ProviderName %>"
InsertCommand="INSERT INTO IDcall(IDcompany, enteredBy, likelihood6mo, liklihood12mo, stamp, objective, dateOfCall, applications, specifics, developmentStage, results, nextStep, salesStrategy, potentialFirstYear, other, valueOfSale, alternateContacts, IDcontact1) VALUES (@company, @entered, @mo6, @mo12, @stamp, @objective, @dateOfCall, @applications, @specifics, @development, @results, @next, @strategy, @potential, @other, @value, @alternate, @IDcontact1)">
<InsertParameters><asp:ControlParameter ControlID="DropDownList_Company" Name="@company"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_signature" Name="@entered"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_6mo" Name="@mo6"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="DropDownList_12mo" Name="@mo12"
PropertyName="SelectedValue" />
<asp:ControlParameter ControlID="TextBox_submittedOn" Name="@stamp"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Purpose" Name="@objective"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_dateOfCall" Name="@dateOfCall"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_applications" Name="@applications"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_specifics" Name="@specifics"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_development" Name="@development"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_results" Name="@results"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_nextStep" Name="@next"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_strategy" Name="@strategy"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_potentialFirstYear" Name="@potential"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_other" Name="@other"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_Value" Name="@value"
PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox_alternateContacts" Name="@alternate"
PropertyName="Text" />
<asp:ControlParameter ControlID="DropDownList_Contact" Name="IDcontact1"
PropertyName="SelectedValue" />
</InsertParameters>
</asp:SqlDataSource>
When I click the button I get Must declare the scalar variable "@company". How is @company not declared?
And here's my stackStack Trace:
[SqlException (0x80131904): Must declare the scalar variable "@company".]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.WebControls.SqlDataSource.Insert() +13
Calls_CallNew.Button1_Click(Object sender, EventArgs e) in c:InetpubwwwrootCallsCallNew.aspx.cs:88
System.Web.UI.WebControls.Button.OnClick(EventArgs e) +105
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +107
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
View 3 Replies
View Related
Apr 18, 2008
I’m getting the error messages: Must declare the scalar variable "@CustomerName".Must declare the scalar variable "@CustomerEmail". When using the code:sqlcommand = "UPDATE Transactions SET CustomerName=@CustomerName WHERE transid='" + thetransid.ToString().Trim() + "' ";
sqlcommand += "UPDATE Transactions SET CustomerEmail=@CustomerEmail WHERE transid='" + thetransid.ToString().Trim() + "' ";
cmd.Parameters.Add("@CustomerName", CustomerNameTextBox.Text);
cmd.Parameters.Add("@CustomerEmail", CustomerEmailTextBox.Text);
cmd = new SqlCommand(sqlcommand, conn);
cmd.ExecuteNonQuery();
conn.Close();
View 8 Replies
View Related
Jan 19, 2008
Hi All,
I'm totaly new to administrating databases.
All I want to do is run the sql server script located at http://www.data-miners.com/sql_companion.htm#downloads.
This creates some tables and uploads a series of text files into them.
When I run the script through SQL Server Express 2005 I get the error Must declare the scalar variable "@DATADIR". I suspect it's something with me putting in the wrong path.
The text files that the script needs to load into the table are located on the K drive, and I have changed the path in
declare @DATADIR varchar(128)
set @DATADIR='C:gordonookdatafinal extfiles'
to
declare @DATADIR varchar(128)
set @DATADIR='k: extfiles'
I suspect this is the wrong syntax that's why it's not working but I might be totally wrong.
The text file and the server are both saved on the k drive.
Regards,
Seaweed
View 3 Replies
View Related
Jun 5, 2015
I have the following code:
begin
SET @CONTADOR = @CONTADOR + 1;
set @strQuery='';
set @intYDS = 0;
SET @strQUERY = 'SELECT @intYDS = ISNULL(COALESCE(YDS,0),0)'
[Code] ....
But when I run returned me the following error: Must declared the scalar variable.
View 3 Replies
View Related
Dec 8, 2007
Hi all,
In my SQL Server Management Studio Express, I have a Database "testDb" that has a dbo.Inventory with a column "quantity". I executed the following sql script:
USE testDb
GO
CREATE FUNCTION AverageQuantity(@funcType varchar(20))
RETURNS numeric AS
BEGIN
DECLARE @average numeric
SELECT @average = AVG(quantity) FROM Inventory WHERE type=@funcType
RETURN @averge
END
GO
I got the following error message:
Msg 137, Level 15, State 2, Procedure AverageQuantity, Line 6
Must declare the scalar variable "@averge".
I think it was declared already in the BEGIN - END block. Please tell me why I got this error and advise me how to fix this problem.
Thanks in advance,
Scott Chang
View 1 Replies
View Related
