Im using the following code in my application. Is it redundent to add the "Top 1" to the select or does it help performance? Thanks
string strSQL = "SELECT TOP 1 c.CusId " +
"FROM COCUS c " +
"LEFT JOIN CONOTITM n ON c.NotId = n.NotId " +
"WHERE c.CusId NOT IN(SELECT CusId FROM RDK_PROSPECT_LOCK WHERE EmpId <> '" + USER.ID() + "') " +
"ORDER BY n.DateUpdate, c.DateUpdate ASC";
I'm trying to do something like the code below, but it's saying "specified cast is not valid" If i change the value returned to an "int", it works fine. My issue is, i'd like to get the value returned with more accuracy than an int as there will be 2 decimal places.protected float getProjectHours(string project) {string selectCmd = "SELECT SUM(hours) FROM tasks WHERE project=@project"; string strConnection = ConfigurationManager.ConnectionStrings["TimeAccountingConnectionString"].ConnectionString;SqlConnection myConnection = new SqlConnection(strConnection); SqlCommand myCommand = new SqlCommand(selectCmd, myConnection);myCommand.Parameters.Add(new SqlParameter("@project", SqlDbType.VarChar));myCommand.Parameters["@project"].Value = project; myConnection.Open();float total = (float)myCommand.ExecuteScalar(); myConnection.Close(); return total; }
Hello, I've got the following code: Dim Selected1 Dim cnn As New Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ToString()) Dim SqlCommand As New Data.SqlClient.SqlCommand("SELECT [OKTipp1] FROM [ErsteSchritte] WHERE (User.Name = @UserName)", cnn) SqlCommand.Parameters.Add("@UserName", Data.SqlDbType.VarChar, 30) SqlCommand.Parameters("@UserName").Value = User.Identity.Name cnn.Open() Selected1 = SqlCommand.ExecuteScalar() cnn.Close() MsgBox(Selected1)Unfortunatly, I am given an error message. What did I do wrong?Thanks for any suggestionsRegards
I keep getting an error message when I try to place the result of an execute scalar command into a text box. The msg is:"conversion from 'DBNull' to type 'String' is not valid" The code I am using is:
Dim con as OLEDBConnection con =NewOleDB connection("Provider = MIcrosoft.JetOLEDB.4.0, Data Source = "c:caps.mdb") Dim cmd As OLEDBCommand cmd= NewOLEDBCommand("Select Product from [Inventory Table] Where [Customer ID] = " & grid View1.SelectdValue texBox1.Text = cmd.ExecuteScalar
The code works fine as long as there is a value for the Product. However if the value in the database is NULL I get an error message: :"conversion from 'DBNull' to type 'String' is not valid". How do I wok around this? Chas28
Basically I want to set chain up the rights so that the anonymous web user IUSR_ .. can execute the new .NET subs, functions etc in the assembly, just as the anonymous web user can execute Stored Procedures when granted. In this way, it should be possible to call the .NET assembly just as classic stored procedures from ASP/ASP.NET.
I have written a .NET function which I can successfully execute if I log on to the database as an administrator by sending this T-SQL query; it returns the result of a given string:
select dbo.CLRHTMLString('abc')
The scenario is now to try to grant access to this assembly for a different role (webuser), which the classic IUSR_MYSERVERNAME is a login of, so that I can call the .NET Assembly when I am authenticated as the anonymous web user (e.g. via ASP, etc.).
To test access, I created a login (webusertest) for a user (webusertest) in the same role (webuser) on the database. But when I use this login, which supposedly has the same rights as the IUSR_, execution right is denied:
EXECUTE permission denied on object 'CLRHTMLString', database 'adt_db', schema 'dbo'.
Note: The 'webuser' database role has Execute permission on the Assembly.
I have also tested this from my actual web page, with the following results: (1) IUSR_MYSERVER member of db_owner role: Web page has right to call assembly. (2) IUSR_MYSERVER not member of db_owner role: Web page does not have right to call assembly.
Further test results: (3) Function can be called when making the user "webusertest" member of the "db_owner" role, which is too much rights to grant for the anonymous web user.
(4) When adding the user 'webusertest' to get 'Execute' permissions on the assembly, it does not get added. After clicking OK, there is no warning message, but when opening the Assembly Properties -> Permission dialog box the same time, the 'webusertest' user does not appear in the list.
I'm having trouble with the UDF's I had in SQL 2000 that were migrated to SQL 2005 under the SCALAR FUNCTION item. Before in 2000 you just created the UDF, named it and saved. IN SQL 2005 you create/modify and then have to save it as a .SQL file to a folder. First of all, what folder should it go to so that all can use and secondly, when I do this and run the functions it doesn't work... I need to modify the scalar function and save it with out saving to a .SQL file. How do I do this or is this the new way to create/modify functions. Any help is much appreciated thanks
Hi everyone, I am getting that infamous message on an INSERT Sql query. I am doing everything right by the looks of it. All variables are either passed in through a custom form, or else declared and initialised in the body of the script. I post the relevent code below: SQLsqlInsertEmail = "INSERT INTO CandidateLogins (SiteID, LoginName, CandidateEmail, DateRegistered) " & _" VALUES (@SiteID, @LoginName, @CandidateEmail, @DateRegistered); SELECT SCOPE_IDENTITY()"Try sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@SiteID", SqlDbType.Int)) sqlSetCandidateEmail.Parameters("@SiteID").Value = SiteID sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@LoginName", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@LoginName").Value = userName sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@CandidateEmail", SqlDbType.VarChar)) sqlSetCandidateEmail.Parameters("@CandidateEmail").Value = email sqlSetCandidateEmail.Parameters.Add(New SqlParameter("@DateRegistered", SqlDbType.DateTime)) sqlSetCandidateEmail.Parameters("@DateRegistered").Value = DateRegistered sqlSetCandidateEmail = New SqlCommand(sqlInsertEmail, C4LConnection) C4LConnection.Open() CandidateID = sqlSetCandidateEmail.ExecuteScalar() Catch Exp As SqlException lblResults.Visible = True lblResults.Text = "Unable to Register Jobseeker: " & Exp.MessageFinallyC4LConnection.Close()End Try All the variables passed into the SQL statement are initialised, with SiteID beign set to '0', rather than Null (none of the fields are Nullable in the database table) and I have checked that the SqlDbType's correspend to the Table Definition So far as I can discern, everything is correct and as can be seen, I am not using a stored procedure in this instance, but the script falls over be producing the error message "Must Declare Scalar @SiteID", even though SiteID is declared as Int32 further up in the script. Any help would be appreciated.
I want to add four fields of a table and place the toatal in a new field.Also I wanna have the average of the fields.
For e.g I have created a marksheet having four subjects.Now I wanna add the subjects and find the average of the subjects and place them in two different fields in the same table along with the respective names in the table.Pls help.
My question is: can I use GETDATE function in scalar UDF as below:
CREATE FUNCTION dbo.GetDateFunction() RETURNS DateTime AS BEGIN RETURN GetDate(); END
I have seen this UDF definition in tutorial,but I thought non-deterministic functions can not be used in UDF.
I also looked at two sites to find out but both says differently:
Technet: [URL] ... states that functions that return different values on each call can not be used in UDF.
MSDN: [URL] .... states that nondeterministic built-in functions can be used in Transact-SQL user-defined functions and in the list of functions GETDATE is listed.
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!
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.
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(); } } }
I have a stored procedure that I'm trying too, and must figure out how to convert it to a view. The stored procedure uses two scalar variable for a date range in the WHERE CLAUSE (@StartDate and @EndDate). What can we do in a view if we cannot hard code date ranges? If I use the date field (Paydate) in the view then I cannot get a sum of the Payamt. I appreciate your help.
CREATE FUNCTION VerificaAcessoPerfil ( @codigo INT ) RETURNS INT
[Code] ....
Curiously when i call my function the same one return always the same value, ex:
Select VerificaAcessoPerfil(2)
the return value is : 698 ??
but if i run the Select statment like this:
SELECT DISTINCT codigo, (case codigo WHEN 1 THEN 695 WHEN 11 THEN 697 WHEN 2 THEN 211 WHEN 10 THEN 698 WHEN 13 THEN 696 WHEN 4 THEN 1 END)[codigo] FROM pf (NOLOCK) INNER JOIN pfu (NOLOCK) ON pfu.pfstamp=pf.pfstamp WHERE codigo IN (1,11,2,10,13,4) ORDER BY 1 ASC
Hey Guys, I am creating a scalar function for the first time. I have googled, ck'd your FAQ's, looked 2 text books but cannot find the answer to my questions. Is the IF statement used in the Scalar function like it is in the stored procedure. In other words does it have to return a Boolean? I want to perform a loop after I call the function, do I have to use a Cursor? Or can I code it like a VB function with a FOR/DO While? Also I can call this UDF from my Stored Proc, right?
Here is a quick synopsis of what I have been assigned to do: --- Calculate Run Dates --(start from Previous Saturday subtract 41 days -- if the 41st day does not fall on a Saturday (day = 7) -- keep subtracting days until Day=Saturday. Add 6 days to make -- END date the following Friday)
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))
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()
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
-- 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
INSERT INTO #TEMP_BENEFIT1 SELECT AssessBenefitID,ProjectBenefitID, Quantity,Quality, Comments FROM PMPT_AssessBenefit WHERE ProjectBenefitID=@ProjectBenefitID AND AssessFlag='E' --and AssessBenefitID=@IterationID
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'
I have a view that contains a complex query. A few of the columnscall a function that returns a specific output. I also use a functionto do a join as well.For example:SELECT l.ID, dbo.getStatus(l.ID) AS statusIDFROM tableName ALEFT OUTER JOIN dbo.Status_LKP s ON dbo.getStatus(l.Leg_ID) =s.statusIDFor 800 records, this query takes about 9 seconds. I realize that foreach record, the function is executed on a per row basis, so I amlooking for alternatives.Does anyone know of other ways to accomplish something of the same?Basically I would like to include UDFs in a query and use those UDFsin the where and join clauses.Thanks
Is it ill-advised to have columns whose values pull from scalar functionsusing other fields in the record as parameters? For example, if I havecreate table a(iID int primary key)create table b(iID int ,iDetail int,CONSTRAINT PK PRIMARY KEY(iID,iDetail),CONSTRAINT FK FOREIGN KEY (iID) REFERENCES a(iID))Let's say in table b I put price information for each detail and in table aI'd like to put a column that sums these prices for the children of eachrecord. Should I make a computed column that references a function usingiID as a parameter? Or would it be better to create a view for this kind ofpurpose?Regards,Tyler
The error message I get is as follows: Invalid use of side-effecting or time-dependent operator in 'OPEN SYMMETRIC KEY' within a function. & Invalid use of side-effecting or time-dependent operator in 'CLOSE SYMMETRIC KEY' within a function.
Here is the code I am trying to implement:
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER FUNCTION [dbo].[func_GetSIMSPassPhrase] ( ) RETURNS varchar(30) AS BEGIN OPEN SYMMETRIC KEY sims_sym_Key DECRYPTION BY ASYMMETRIC KEY sims_asym_key DECLARE @GUID UNIQUEIDENTIFIER SET @GUID = (SELECT key_guid FROM sys.symmetric_keys WHERE name = 'sims_sym_Key') DECLARE @passphrase varchar(30) SELECT @passphrase = (SELECT CAST(DecryptByKey(EncField) AS VARCHAR(30)) FROM tblEncryptTest) CLOSE SYMMETRIC KEY sims_sym_Key RETURN @passphrase END
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----------------------------------------------------------------------------------------------------------------
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
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 %>"
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();
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
--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
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.
I have a scalar function, which calculates the similarity of two strings. I use the following query, to compare the entries of one table against the value 'Test' and return the entries, which have a value > 50:
;WITH cte1 AS ( SELECT b.FirstName, (SELECT fn_similarity('Test', b.FirstName)) AS [Value], b.LastName FROM [AdventureWorks2012].[Person].[Person] b )
SELECT * FROM cte1 WHERE [Value] > 50.00 ORDER BY [Value] DESC
Now I want to use this query against the first 50 entries of the [Person] table, so that the resultset includes all the values of the first 50 persons and the entries, which are similar to them.
At the moment I use a WHILE-loop and write the five single resultsets in a temporary table. Is there another way / a better way, maybe via a join?
Hello there!I am working with MS SQL Server 2000.I have a table function that takes an integer parameter and returns atable, and I can successfully use it like this (passing a literalas a parameter):SELECT * FROM MyTableFunction(1)or like this (passing a variable as a parameter):DECLARE @i AS intSELECT @i = 10...SELECT * FROM MyTableFunction(@i)Now, if I have another function -- a scalar function that returns aninteger, I cannot find a way to use it to specify the parameter valuefor the first function. For example, when I writeSELECT * FROM MyTableFunction( dbo.MyScalarFunction() )SQL Server issues the following complaint:Incorrect syntax near '.'.I am really perplexed: what I am doing wrong?Interestingly, if I re-write the second snippet asDECLARE @i AS intSELECT @i = dbo.MyScalarFunction()...SELECT * FROM MyTableFunction(@i)everything works just fine; however, this trick cannot be used as aworkaround because I need to pass result of one function as a parameterto another inside a view's code -- I cannot declare variables and writeany procedural code...Any ideas, especially workarounds, would be greatly appreciated.Thank you,Yarik.
