Solved

sql server update procedure

Posted on 2016-07-27
6
42 Views
Last Modified: 2016-07-27
I dont know the syntax, I have this so far I think you can see what I want to do.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,
SET StrShortTagged = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

0
Comment
Question by:PeterBaileyUk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
6 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 41731348
>I have this so far I think you can see what I want to do.
The syntax looks correct, and I hate to guess what users' requirements are, so why don't you tell us what you're trying to pull off here, and any error messages that are displayed when you execute the above code.
0
 

Author Comment

by:PeterBaileyUk
ID: 41731366
as it was just to understand the syntax for an update procedure I didnt expand, I will pass the client code, and the other two variables in vb.net app and it update the word table.

The vb code is here but I havnt made the call to the procedure yet. so a bit of a mute point.

the code creates the two strings i want to send to the table words

   Public Sub CreateShort()
        Dim dtCodes = New DataTable
        Dim dtCodeWords = New DataTable
        Dim dtCodeWordsT = New DataTable
        Dim cmd As New SqlCommand
        Dim CmdWords As New SqlCommand
        Dim StrClientCode As String
        Dim StrProcName As String
        Dim StrShortString As String
        Dim StrShortTaggedString As String

        StrProcName = "usp_getUnTaggedCodeWords"
        Using cnSql As New SqlClient.SqlConnection("Data Source=MAIN-PC\SQLEXPRESS;Initial Catalog=Dictionary;Integrated Security=True;MultipleActiveResultSets=True")

            cnSql.Open()
            cmd.Connection = cnSql
            cmd.CommandText = "SELECT TblWords.ClientCode FROM TblWords LEFT JOIN TblWordTags ON TblWords.ClientCodeWordPosition = TblWordTags.ClientCodeWordPosition WHERE (((TblWordTags.ClientCodeWordPosition) Is Not Null)) GROUP BY clientcode;"

            Dim reader As SqlDataReader = cmd.ExecuteReader()
            dtCodes.Load(reader)

            For Each drRecord As DataRow In dtCodes.Rows

                StrClientCode = drRecord("ClientCode")
                If StrClientCode = "069558C" Then
                    MessageBox.Show("now")
                End If

                dtCodeWords = GetShortStringWordsData(StrClientCode, False)

                For Each drRecordW As DataRow In dtCodeWords.Rows
                    StrShortString = StrShortString & drRecordW("word") & Chr(32)
                    StrShortString = RTrim(StrShortString)
                    Debug.Print(StrShortString)
                Next

                dtCodeWordsT = GetShortStringWordsData(StrClientCode, True)

                For Each drRecordWT As DataRow In dtCodeWordsT.Rows
                    StrShortTaggedString = StrShortTaggedString & drRecordWT("word") & Chr(32)
                    StrShortTaggedString = RTrim(StrShortTaggedString)
                    Debug.Print(StrShortTaggedString)
                Next







            Next


        End Using
    End Sub

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41731373
ive attached the screenshot form sql server it has red underlines so the syntax must be wrong
ee.JPG
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 65

Accepted Solution

by:
Jim Horn earned 500 total points
ID: 41731379
Second thought, I see the error, the second SET statement should not be there.  In a single UPDATE with multiple columns the columns are separated only by commas.

USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]
(
@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL
)
AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged,  StrShortTagged = @StrShortTagged  -- No SET before the second column
WHERE ClientCode = @ClientCodeIn
END
GO

Open in new window

0
 

Author Comment

by:PeterBaileyUk
ID: 41731394
ok I worked it out myself;
USE [Dictionary]
GO

CREATE PROCEDURE [dbo].[usp_UpdateShortStrings]

@ClientCodeIn nvarchar(50) = NULL,
@StrShortUnTagged nvarchar(Max) = NULL,
@StrShortTagged nvarchar(Max) = NULL

AS
BEGIN
UPDATE dbo.TblWords
SET StrShort = @StrShortUnTagged, StrShortTag = @StrShortTagged 
WHERE ClientCode = @ClientCodeIn
END

Open in new window

0
 

Author Closing Comment

by:PeterBaileyUk
ID: 41731395
Thank you I experimented before i saw your answer, its fine now thx
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
connection to SQL 2012 error in windows 10 18 49
semaphore timeout period has expired 1 33
SQL Query 20 25
first parameter x of the decimal (x, y) 1 30
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

710 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question