Verify If The Input Param Exists In A Table? - How?
Dec 9, 2007
hello,
i have a table in witch i store the favorites pictures for each user, like so:
FavID PictureID UserId (uniqueidentifer)
users can add a picture at favorites by clicking a button from the picture table ("Add to favorites")
i need a stored procedure witch uses two input params (userId and PictureID), witch should return 1 if the relations between the PictureID and UserId already exists in the table(avoid having same relation again) or 0 if not (and i'll use that result to make the button enabled or disabled)
I need to write a stored procedure to verify that a table exists andalso that the user executing the stored procedure has access to thespecified table.Any user can call this publicly available procedure and pass a databasename, an owner name and a table name as parameters. The procedurereturns success if the table exists and the user has access to it, orfails if he doesn't. Here's a simplified version of what I have, butI'm wondering if there's a better way. Thanks.create procedure dumb asbegindeclare @myError int,@mytable varchar(128),@myquery varchar(128)select @mytable = '[Northwind].[dbo].[sysobjects2]'select @myquery = 'DECLARE @x int SELECT @x = count(1) from ' +@mytable + ' where 1 = 2'exec (@myquery)select @myError = @@ERRORif @myError != 0BEGINRAISERROR ('ERROR: The specified table %s cannot be accessed.', 10, 1,@mytable)RETURN 1endendgo
Hi,Is there a simple way to verify if a database exists?I'm writing a stored procedure that will accept a database name as an inputparameter,and create the database if it does't already exist.--Message posted via http://www.sqlmonster.com
In a Database "AP" of my SQL Server Management Studio Express (SSMSE), I have a stored procedure "spInvTotal3":
CREATE PROC [dbo].[spInvTotal3]
@InvTotal money OUTPUT,
@DateVar smalldatetime = NULL,
@VendorVar varchar(40) = '%'
This stored procedure "spInvTotal3" worked nicely and I got the Results: My Invoice Total = $2,211.01 in my SSMSE by using either of 2 sets of the following EXEC code: (1) USE AP GO --Code that passes the parameters by position DECLARE @MyInvTotal money EXEC spInvTotal3 @MyInvTotal OUTPUT, '2006-06-01', 'P%' PRINT 'My Invoice Total = $' + CONVERT(varchar,@MyInvTotal,1) GO (2) USE AP GO DECLARE @InvTotal as money EXEC spInvTotal3 @InvTotal = @InvTotal OUTPUT, @DateVar = '2006-06-01', @VendorVar = '%' SELECT @InvTotal GO //////////////////////////////////////////////////////////////////////////////////////////// Now, I want to print out the result of @InvTotal OUTPUT in the Windows Application of my ADO.NET 2.0-VB 2005 Express programming. I have created a project "spInvTotal.vb" in my VB 2005 Express with the following code:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Public Class Form1
Public Sub printMyInvTotal()
Dim connectionString As String = "Data Source=.SQLEXPRESS; Initial Catalog=AP; Integrated Security=SSPI;"
Dim conn As SqlConnection = New SqlConnection(connectionString)
Try
conn.Open()
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "[dbo].[spInvTotal3]"
Dim param As New SqlParameter("@InvTotal", SqlDbType.Money)
param.Direction = ParameterDirection.Output
cmd.Parameters.Add(param)
cmd.ExecuteNonQuery()
'Print out the InvTotal in TextBox1
TextBox1.Text = param.Value
Catch ex As Exception
MessageBox.Show(ex.Message)
Throw
Finally
conn.Close()
End Try
End Sub
End Class ///////////////////////////////////////////////////////////////////// I executed the above code and I got no errors, no warnings and no output in the TextBox1 for the result of "InvTotal"!!?? I have 4 questions to ask for solving the problems in this project: #1 Question: I do not know how to do the "DataBinding" for "Name" in the "Text.Box1". How can I do it? #2 Question: Did I set the CommandType property of the command object to CommandType.StoredProcedure correctly? #3 Question: How can I define the 1 output parameter (@InvTotal) and 2 input parameters (@DateVar and @VendorVar), add them to the Parameters Collection of the command object, and set their values before I execute the command? #4 Question: If I miss anything in print out the result for this project, what do I miss?
I am looking for a Sql query to verify the inserted values from one table(in CSV file) with another table(in sql database)
For example: I have below Values column that is present in once CSV file, after my data migration the values get stored in Results table under Message column.
I need to very whether values(1X,1Y) are inserted in Message record "successfully inserted value 1X"
Values (CSV) 1X 1Y
Results Table(SQL) CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point 2015-08-04 08:35:29.203 successfully inserted value 1Y 2015-08-04 08:30:29.203 Test Pass 2015-08-04 08:28:29.203 successfully inserted value 1Y
If all values are inserted:
Output: All values from values table are inserted successfully Total count of values inserted: 2 If only few values are inserted, example only 1X from Values table is inserted in Message
Example: Results Table CreatedDate Message 2015-08-04 08:45:29.203 successfully inserted value 1X 2015-08-04 08:44:29.103 TEst pass 2015-08-04 08:43:29.103 successfully inserted value 1X 2015-08-04 08:42:29.203 test point
Output: All values from values are not inserted successfully in result table. Total count of values inserted: 1 Missing Values not inserted in results table are: 1Y
This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).
I'm looking at these new databases and I'm seeing code similar to this all over the place:
if not exists (select 1 from dbo.t1 where f1 = @p1) begin select @errno = @errno | 1 end
There's a unique clustered in dex on t1.f1.
The execution plan shows this for this statement:
FROM TABLE dbo.t1 EXISTS TABLE : nested iteration. Table Scan. Forward scan. Positioning at start of table.
It's not using my index!!!!!
It seems to be the case with EXISTS statements. Can anybody confirm?
I also hinted to use the index but it still didn't use it.
If the existence check really doesn't use the index, what's a good code alternative to this check?
I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.
SET ROWCOUNT 1 SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01) WHERE f1 = @p1 ) SET ROWCOUNT 0
I also have the same error, I am trying to do two things in my Stored Proc.
1) - Insert a parent record. 2) - Insert 1 or many records in the child table using the parent ID
The child records are being passed as a XML param.
I am also getting the 'Subqueries are not allowed in this context. Only scalar expressions are allowed.' error when I try to create the procedure.
SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- ============================================= -- Author:pryder -- Create date: -- Description: -- ============================================= CREATE PROCEDURE TestProc -- Add the parameters for the stored procedure here @Name String , @bings XML AS BEGIN transaction -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; declare @NewID as int declare @err as int -- Insert statements for procedure here INSERT INTO PARENT (Name) VALUES (@Name) SELECT @err = @@error if @err <> 0 begin rollback transaction return @err end
SELECT SCOPE_IDENTITY = @NewID
INSERT INTO Child (ParentID, name)
Values
(( SELECT @NewID, ParamValues.ID.value('.','VARCHAR(MAX)') FROM @bings.nodes('bings/group') as ParamValues(ID) ))
SELECT @err = @@error if @err <> 0 begin rollback transaction return @err end
Previously same records exists in table having primary key and table having foreign key . we have faced 7 records were lost from primary key table but same record exists in foreign key table.
I started with an inline table returning function with a hard coded input table name. This works fine, but my boss wants me to generalize the function, to give it in input table parameter. That's where I'm running into problems.
In one forum, someone suggested that an input parameter for a table is possible in 2012, and the example I saw used "sysname" as the parameter type. It didn't like that. I tried "table" for the parameter type. It didn't like that.
The other suggestion was to use dynamic sql, which I assume means I can no longer use an inline function.
This means switching to the multi-line function, which I will if I have to, but those are more tedious.
Any syntax for using the inline function to accomplish this, or am I stuck with multi-line?
A simple example of what I'm trying to do is below:
Create FUNCTION [CSH388102].[fnTest] ( -- Add the parameters for the function here @Source_Tbl sysname ) RETURNS TABLE AS RETURN ( select @Source_Tbl.yr from @Source_Tbl )
Error I get is:
Msg 1087, Level 16, State 1, Procedure fnTest, Line 12 Must declare the table variable "@Source_Tbl".
If I use "table" as the parameter type, it gives me:
Msg 156, Level 15, State 1, Procedure fnTest, Line 4 Incorrect syntax near the keyword 'table'. Msg 137, Level 15, State 2, Procedure fnTest, Line 12 Must declare the scalar variable "@Source_Tbl".
I need to find out if a Transaction ID exists in Table A that does not exist in Table B. If that is the case, then I need to insert into Table B all of the Transaction IDs and Descriptions that are not already in. Seems to me this would involve If Not Exists and then an insert into Table B. This would work easily if there were only one row. What is the best way to handle it if there are a bunch of rows? Should there be some type of looping?
I have a textbox1 control where a zip code is entered. What is the most efficient way in C# code behind to see if that zip code exists in tblZipCode? I only need to know if it is there, do not need to know how many occurances.
HI, having a problem with a data loading from 2005 to 2000. Im using export & import wizard. But the problem comes when the user drops a table on 2005. Anyway to check if table exista then use the E&I to load the data. I cant use the BCP as xp_shellcmd is disable.
Hi all,I am in the process of creating a page that checks to see if a particular user exists in the database and if it does then send to welcome.aspx if not then accessdenied.aspx. The userid is requested from the query string.I have done some research and cannot find anything directly related, I have tried to add bits of code into what i think is the right place but I dont think what i am doing is correct. Can someone help and show me where my code is going wrong? Also is there a better/more efficient way to do what I am doing?Thanks in advance. default.aspx.csusing System;using System.Data;using System.Data.SqlClient;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;public partial class _Default : System.Web.UI.Page{ protected void Page_Load(object sender, EventArgs e) { string UserID = Request.QueryString["uid"]; //string TransferPage; if (UserID != null) { //initiate connection to db SqlConnection objConnect = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["MyConnection"].ConnectionString); string sql = "SELECT COUNT(*) FROM members WHERE UserID = '" + UserID + "'"; SqlCommand query = new SqlCommand(cmd, test); int count = (int)query.ExecuteScalar(); int aantal = -1; // some default value if can't insert record if (count == 0) // no existing record for username { Server.Transfer("accessdenied.aspx"); } else { Session["UID"] = UserID; Server.Transfer("welcome.aspx"); } } }}
i am using vb.net and ms sql server 2005 express.....what is the syntax for dropping a table if existsi have used this but it says incorrect syntax near if Dim cmda As New SqlCommand("drop table " + test + " if exists", New SqlConnection(strdb)) cmda.Connection.Open() cmda.ExecuteNonQuery() cmda.Connection.Close()any solutions???? plz only answer in vb.net and sql server express
What is the best way to programmatically determine if a temp table exists? In 6.5, I would use
IF EXISTS(SELECT * from tempdb..sysobjects where id = object_id('tempdb..#MyTable') and type = 'U')
But now that it is strongly discouraged to code against system tables, how could I re-write this statement?
The proper way to check the existence of a table would be:
IF OBJECTPROPERTY(object_id('tablename'), 'IsTable') = 1
However, to get this to run for a temp table, I think you'd have to change the database context to tempdb and then back to your database. That doesn't seem efficient.
I could use
IF object_id('tempdb..#MyTable') IS NOT NULL
But that's not guarenteeing that it's a table, right?
How do I find out if a temporary table named '##test' exists? I have a stored procedure that creates this table and if it exists another stored procedure should do one thing, if it does not exist I want the SP to do something else. Any help as to how I can determine if this table exists at the current time would be greatly appreciated.
This my first time using the link server to read the files directly from within SQL2005, so issues the following link:-
EXEC sp_addlinkedserver @server = N'MYVFPSERVER', -- Your linked server name here @srvproduct=N'Visual FoxPro 9', -- can be anything @provider=N'VFPOLEDB', @datasrc=N'"C:PROGRAM FILESMICROSOFT VISUAL FOXPRO 9Samplesdata estdata.dbc"'
After that i can open query and do the import issues, but how can check if the table exists before issues the query.
Hi,I am trying to create a script that deletes transaction tables and leavesmaster data like customer, vendors, inventory items, etc. How can I useTRUNCATE TABLE with an Exists? My problem is I have 200+ tables, if Isimply use a list like:truncate table01truncate table02truncate table03....I get errors if the table does not exist and have to manually run thetruncate statements. Some tables may not exist if that part of the app isnever used. I'm trying to make a list of all tables that could existwithout it erroring out 50+ times.Thanks in advance.
What’s the easiest way to check if a single value exists in a table column? I’m building a simple login page, and I want to get the username and check if it exists in my Users table. Here’s my SQL: SELECT UserID FROM UsersWHERE UserID = "admin" Could someone give me code to check for “admin” in my UserID column? Here’s some code I tried, using my SqlDataSource, but it returns an error “Could not load type 'System.Data.OleDb'” protected void Button1_Click(object sender, EventArgs e) { // Retreive the results from the SqlDataSource as a DataReader OleDbDataReader reader = (OleDbDataReader) SqlDataSource1.Select(DataSourceSelectArguments.Empty); // Read in the value if (reader.Read()) { } // Close the reader reader.Close(); } I don’t have to use the SqlDataSource, but originally thought it might be easier. I know how to use SqlDataSource to fill a whole GridView but this is different.
Basically, i am still relatively new to ASP.net and SQL. And i have the following query. I have a skills table to which the user enters their skills using the following fields: Skillcatagory, SKill, Current Level, Target Level, target date and comments and the serial of the user. I need to check via our staff table, which people have had a skill entered for them. And then produce a report on who has not had a skill entered for them. This table has a serial of the user column aswell which is unique. If there is more information that i can give you to assist me, please ask me. You help would be greatly appreciated.
I need to do some processing but only if a specific table 'table1' exists in the SQL Server database. How can I check if this 'table1' exists using either ADO.Net code or SQL Server query?
I've got two tables, one containing a list of company names(approx 10,000 records), the other containing a list of company employees (approx 30,000 records) joined by the CompanyID column.
I have a third table (approx 700 records) containing new employees to be added to the employee table. Each record in this table has the employees details plus the name of their company.
I want to write a query that will check each row in the third table to see if a) the employee exists in the Employees table b) the company exists in the Companies table and c) the employee is listed under the correct company
The query should also handle any combination of the above. So if the company doesn't exist but the employee does, create the company on the companies table and update the appropriate record on the employees table with the new CompanyID etc. etc.
Oh, forgot to mention. The company names in the third table won't be exactly the same as the ones in the Company table so will need to use CharIndex.
I have 2 test tables one for stock and one for prices.
I need to select all the rows from Stock but also the price on the stock but the price table doesn't always have the date, so I can not do stock date = price date.
What it needs to do is if the Stoc Date isn't in the price table use the price before... this would also have to be able to run on my rows...
-- Create Test Table (not sure if dates USA or UK format on your machine...
So I declared this table data type , cool.. It works
CREATE TYPE BP_Data_XXX as table ( XXX_VALUE_NUMERIC numeric(19,2), bp_type VARCHAR(4), Dt datetime, ID int IDENTITY(1,1), MBP numeric(19,2), CONCEPT_ID VARCHAR(10) )
Now my question is later how do we check whether this type exists. The following does not work.
if( object_id('BP_Data_XXX') IS NOT NULL ) print 'IT is there '
Hi there, I want to create an SQL Function that checks if a table exists and returns true or false. I will pass this function a paramter (say @COMPANYID) e.g.
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblmyTableName_' + @COMPANYID) and OBJECTPROPERTY(id, N'IsUserTable') = 1)
how would I write this so the query is dynamically executed and I can get a value of true or false back?
HelloI am using a temp table called ##temp in an SProc but often get themessage that the table already exists. Could this be because the SProcis being run by more than 2 webpages at the same time?Or is it because the sProc has an error and is not getting to the droptable line?I have tried adding a line to test if the object exists and to drop thetable before I create it. If I drop it will it affect another instanceof the sProc that is using the same table name?If so is there a way around this?Many thanksNigel
